Want last names sorted first, then first names? In Access Query Design, put LastName to the left of FirstName and set both Sort cells to Ascending. Save the query so you can reuse that order. Watch the video for sorting in tables, queries, and forms.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/2381.
Need a total for just the records that matter? DSum adds a field from a table or query, using criteria to choose which records count. Here the paid orders add up to $200; the unpaid order stays out. Use Nz to show zero instead of Null when nothing matches. Watch the video to add date criteria and build paid and unpaid totals.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/DSum.
Think you know your way around password prompts in Microsoft Access? This little quiz covers five common issues that come up when you want to protect a form, look up a shared password, and keep users from wandering into payroll just because they clicked a button.
Grab a piece of paper, keep score if you like, and see how you do. The important thing to remember is that a simple password prompt can be useful for controlling the normal route through your application, but it is not the same thing as real database security. Access will happily let a determined user find the plumbing if you leave the basement door unlocked.
Question 1: A macro opens Payroll F even when the password entered by the user does not match the expected password. What makes the form open only when the passwords match?
A. Move OpenForm above the If block.
B. Put OpenForm in an Else branch.
C. Show "incorrect password" before OpenForm.
D. Move OpenForm inside the matching-password If block.
The correct answer is D. The OpenForm action has to be inside the part of the macro that runs only when the entered password matches the expected password. If OpenForm sits after the If block, Access will run it whether the comparison succeeds or fails. That is a very common beginner mistake: the password check looks right, but the protected action is not actually controlled by it.
Question 2: Why store a shared application password in one settings table instead of typing it directly into every button macro?
A. It automatically hides the password from users.
B. One saved change can be reused by every macro that looks it up.
C. It removes the need to compare the user's entry.
D. It automatically refreshes every open form.
The answer is B. Centralizing a setting means you can change it once instead of opening ten different macros and hoping you did not miss one. This applies to more than passwords, too. Things like company names, tax rates, default folders, email addresses, and other shared application settings are usually much easier to manage from one location.
Question 3: An Access macro uses an InputBox to request a password. Users should see asterisks instead of the actual characters they type. Which approach supports this?
A. Use a custom form with a password-masked text box.
B. Apply a password input mask only to the table field.
C. Put asterisks in the InputBox default response.
D. Change the InputBox title to "Password."
The correct answer is A. The built-in Access InputBox does not provide password masking. Changing the caption, filling the default answer with asterisks, or adding an input mask to some table field will not change what users see while typing in that InputBox.
If you want the familiar password behavior where each typed character appears as an asterisk or dot, use a small custom form with a text box configured for password entry. It gives you control over the user experience, lets you add OK and Cancel buttons, and makes the prompt reusable anywhere in your database. The embedded video discusses the general approach.
Question 4: A menu button asks for a password before opening a payroll form. Users can still open the table containing that password as plain text. What protection does that button alone provide?
A. It encrypts the entire database.
B. It prevents users from opening every table.
C. It checks that menu button route, not every access path.
D. It prevents users from copying the database file.
The answer is C. A password check in a button macro protects that particular route through your application. It does not magically secure the tables, forms, queries, VBA modules, navigation pane, or database file. It is a speed bump, not Fort Knox.
This kind of prompt can still be perfectly reasonable for keeping ordinary users out of management screens or preventing accidental access. Just do not confuse it with proper security. If users can open the settings table or inspect your database design, a plain-text password is not much of a secret.
Question 5: DLookup retrieves one value from a table. Settings T contains one password record per department. How can a macro reliably retrieve the payroll password?
A. Sort payroll to the top.
B. Add criteria for the unique payroll row.
C. Rename the receiving variable.
D. Run the same lookup twice.
The correct answer is B. A DLookup should include criteria that identifies the correct record. If your settings table holds passwords for Payroll, Management, Sales, and other departments, do not rely on whichever record Access happens to return first. Tell Access exactly which row you want.
For example, the lookup needs to identify the Payroll record using a unique field or clear criteria. A lookup without criteria may appear to work during testing, right up until someone adds another record and your payroll password suddenly becomes the password for the snack committee.
If you got all five, congratulations. You are well on your way to keeping the normal users out of the places they should not be clicking. If you missed a few, watch the embedded video for the quiz walkthrough and check out Microsoft Access Advanced Level 4, Lesson 1 for the full password-prompt lesson.
Live long and prosper,
RR
Need a query to show a label based on a value? IIf takes a condition, a result when it is true, and a result when it is false. In this example, a credit limit of $1000 or more displays High Credit. Anything lower displays Normal. The query calculates that label without storing it in the table. Watch the video for more ways to use IIf in your Access queries and forms.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/IIF.
Two hours and 30 minutes is 2.5 hours. Calculate minutes with DateDiff("n", [TimeIn], [TimeOut]), then divide by 60 for decimal hours you can total. The "n" means minutes; "m" means months. In my Total Time Spent video, I show how to calculate and display work time, then total it by job.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/1391.
Need to change values, add records, remove records, or create a new table? Pick the action query that matches the job. This quick reference shows the four types and an important reminder: back up your database first. Watch the video for the overview.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/1550.
Some performers entertain us for a couple of hours. Others become part of the background of our lives, showing up during the years when we are still figuring out who we are and what kind of person we want to become. For me, Patrick Stewart was one of those people. He will always be Captain Jean-Luc Picard, but the lessons I took from that character went far beyond Star Trek.
I have been a Star Trek fan for as long as I can remember. I grew up watching the original series in reruns, fascinated by the Enterprise, the computers, the science, and the idea that humanity might someday reach the stars instead of just arguing about parking spaces and cable bills.
When I was a little kid, though, Star Trek was mostly spaceships, alien planets, phasers, and Captain Kirk getting into trouble. The deeper ideas were there, of course, but most of them sailed right over my eight-year-old head at warp speed.
That changed when Star Trek: The Next Generation premiered in 1987. I was 14 years old, which is about the age when a young man starts trying to understand what adulthood is supposed to look like. I was already becoming interested in more serious science fiction and fantasy, and I was ready for stories that had more going on than explosions and bad guys in funny rubber masks.
Then along came Captain Jean-Luc Picard.
My parents divorced when I was young, and I did not see much of my father while I was growing up. We have a wonderful relationship now, but during my childhood, my grandparents were the people raising me. My grandfather was the man I looked up to most, and he passed away a few years before The Next Generation began.
I am not suggesting that a television character could replace a grandfather. Nobody can do that. But Picard became a role model at a time when I needed one. Every week, there was this intelligent, composed, thoughtful man sitting in the captain's chair, trying to do the right thing in situations where the right answer was rarely obvious.
That mattered.
Picard was not the loudest person in the room. He did not solve every problem by punching it, shooting it, or dramatically flipping over a conference table. In fact, he often did the opposite. He listened. He asked questions. He considered the consequences. He relied on the people around him. And when necessary, he stood his ground.
There is a real lesson in that, especially for young men. Being strong is not the same thing as being angry. Leadership is not barking orders. Intelligence and compassion are not weaknesses. Sometimes the bravest thing you can do is take a breath, listen to someone you disagree with, and make the best decision you can with the information you have.
Picard was not perfect, either, and that is one reason he worked so well. He could be stubborn. He could be distant. He made mistakes. Sometimes he carried the burden of command too heavily. But he tried. He reflected. He learned. That is a much better example than some flawless superhero who never has to question himself.
The Next Generation ran from 1987 to 1994, which meant Captain Picard was part of my life from age 14 through 21. Those are not exactly unimportant years. They are the years when you start deciding which values you want to keep, what kind of adult you hope to become, and how you will behave when life gets complicated.
Years later, after becoming a father myself, I appreciated those lessons in a different way. How do you teach your children to be decent human beings? How do you lead by example instead of simply telling everyone else what to do? I cannot claim that I have always lived up to Picard's standard. Nobody gets through life without making a few decisions they would very much like to take back. But it is a good standard to aim for.
My kids were definitely exposed to plenty of Star Trek and Rush growing up. I figured that if Captain Picard and Neil Peart helped shape me, they were not a terrible influence to have around the house. Whether the kids always appreciated that educational program is another matter entirely.
Over the years, I also came to admire Patrick Stewart himself. That is an important distinction. Jean-Luc Picard was a fictional character created by talented writers and brought to life by a remarkable actor. Patrick Stewart was a real person, with his own history, struggles, convictions, and choices.
His memoir, Making It So, is well worth reading. Stewart grew up in a working-class family in Yorkshire, England, and his childhood was not easy. He witnessed domestic violence in his home, an experience that remained with him for the rest of his life. Rather than simply burying those memories, he used his public voice to support organizations helping women experiencing domestic violence and veterans dealing with trauma.
That kind of compassion is not simplistic. It does not excuse harmful behavior. It recognizes that human beings are complicated, that trauma has consequences, and that understanding the causes of suffering can help us prevent more of it. That is very much in keeping with the best of what Star Trek has always tried to say.
He also used his platform to support equality and LGBT rights. His friendship with Sir Ian McKellen was one of those things that always made me smile. Professor X and Magneto being close friends in real life is proof that perhaps humanity still has a chance after all.
And then there were the dogs. Anyone who knows me knows that I am a dog person. Patrick Stewart and his wife, Sunny Ozell, fostered rescue dogs and helped draw attention to the need for adoption, especially for pit bulls and other animals that too often get judged before anyone gives them a chance. Seeing a distinguished Shakespearean actor completely melted by a rescue dog tells you something good about the man.
Of all the performances Stewart gave during his career, the one that has stayed with me most is from the Star Trek: The Next Generation episode "The Inner Light." If you have never seen it, watch it. You do not need to be a Star Trek fan. You do not need to know anything about the Enterprise. It is simply one of the finest hours of television ever made.
In the episode, Picard is struck by a mysterious probe and suddenly finds himself living the life of another man on a distant world. At first, he is confused and insists that he is Captain Picard of the Enterprise. But time passes. He builds a life. He falls in love, raises children, grows old, and learns to play a simple flute.
Eventually, he learns that the civilization he has experienced is dying. The people sent the probe into space hoping that, someday, someone would carry their memories. Then Picard awakens aboard the Enterprise, where only minutes have passed. For him, however, decades have gone by. He has lived an entire lifetime that nobody around him can understand.
Near the end, Commander Riker gives Picard a small box recovered from the probe. Inside is the flute from that other life. Picard sits alone and begins to play it.
That scene still gets me every time. There is no battle, no big speech, no dramatic order from the captain's chair. It is just a man quietly remembering people he loved, a family he raised, and a world that no longer exists except in his memory.
That is what makes "The Inner Light" so powerful. It is about life, love, family, loss, and memory. It is about the idea that something can survive even after it is gone, as long as someone remembers it and carries its story forward.
Great artists do that, too. They give us stories, characters, music, performances, and ideas that become part of our own lives. To some people, Patrick Stewart will always be Professor Xavier. To others, he will be a towering Shakespearean actor. To me, he will always be Jean-Luc Picard, the captain who taught a teenage boy that kindness, intelligence, humility, and integrity were qualities worth aspiring to.
I never had the opportunity to meet Patrick Stewart or tell him directly what his work meant to me. But I hope he understood how many people he inspired. He was not merely entertaining audiences. Through the characters he played and the example he set in his own life, he helped people imagine a better version of themselves.
Captain Picard will always be there in that center seat on the bridge of the Enterprise, ready for another impossible problem, another strange new world, and another chance to show that doing the right thing is worth the effort. Somewhere in his quarters, there is a small wooden box with a simple flute inside, holding the memory of another lifetime.
That is how I will remember him.
Bon voyage, mon capitaine. And thank you for everything.
If you would like to hear the full story and my thoughts on Patrick Stewart, Captain Picard, and the episode that still makes me choke up after all these years, watch the embedded video above.
Live long and prosper,
RR
