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
Round, Int, and Fix can give you different answers from the same number. Int always goes down to the lower integer, while Fix goes toward zero. Round chooses the nearest integer, with exact halfway values going to even. Watch the video to see these differences in an Access query and learn more about rounding.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/2282.
Looking for a number somewhere in a note is not the same as checking the entire field. [0-9]+ finds a run of digits, while ^[0-9]{5}$ checks for exactly five digits from start to finish. That checks the format, not whether a ZIP code exists. Watch the video to see both tests in Access VBA.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/5807.
Imported text data is rarely as clean as we would like it to be. Customer notes, copied emails, contact information, order details, and comments from other systems all tend to arrive in one big messy pile. Somewhere in that pile might be an invoice number, a phone number, a ZIP code, or some other useful value you need to work with. You can certainly attack it with Left, Right, Mid, InStr, and a small army of loops, but regular expressions give you a much better tool for many of these jobs.
Regular expressions, usually called regex, are patterns used to search text. Instead of telling Access exactly where a value starts and stops, you describe what the value looks like. A pattern can say, "find five digits," "find a phone number with optional punctuation," or "find an invoice reference that begins with INV." That is a whole lot more flexible than hoping every user typed something the same way. Spoiler alert: they did not.
Microsoft Access does not have a built-in Regex function sitting next to Left and Mid, but Access VBA can use the regular expression engine that comes with Windows. Once it is set up, you can use regex patterns to search text, validate an entire value, extract matching information, and replace bad formatting with good formatting.
That last part is especially handy when you are dealing with imported data. Maybe one customer record has a phone number written as 555-123-4567, another has (555) 123 4567, and someone else entered 555.123.4567. A regex pattern can recognize each of those as the same basic kind of value. You can then reformat them consistently without manually fixing hundreds of records.
A simple regex pattern can search for digits. From there, patterns become more descriptive. You can specify how many characters should appear, whether certain characters are optional, whether a match must occur at the beginning or end of the value, and whether uppercase and lowercase letters should be treated as the same. This makes regex useful for jobs like validating ZIP codes, checking ID formats, locating email-like text, and finding numbers buried inside long notes.
One important distinction is between finding a match and validating a value. If you are searching a customer note, you may simply want to know whether the note contains something that looks like an invoice number. If you are validating data entered into a form field, you usually want the entire field to match the required pattern. Those are different tasks, and regex gives you the controls to handle both.
For example, a note may contain a date, a dollar amount, an order number, and an invoice number all in the same paragraph. Searching for "some digits" is not enough, because you will get every unrelated number too. A better pattern looks for the label or structure surrounding the invoice value. It can recognize that users may type Invoice, INV, a number sign, a colon, or a dash before the actual number. That is where regex starts earning its keep.
The class begins with the basics of patterns and matching, then moves into practical Access VBA work. You will see how to find a matching value, pull out a value you need, find multiple matches in the same piece of text, and control case sensitivity when appropriate. These are the building blocks you need before attempting the more interesting cleanup jobs.
Rather than writing one-off regex code every time you need it, the course also builds reusable functions. That is the sensible approach. Once you have a reliable function for testing a pattern, extracting matches, or replacing text, you can call it from forms, reports, queries, import routines, and other VBA procedures. You should not have to reinvent the wheel every time an imported spreadsheet decides to be creative.
You will also learn how to maintain a library of commonly used patterns. Regex syntax is powerful, but it is not exactly something most people memorize for fun. Keeping patterns for phone numbers, ZIP codes, invoice references, and other common formats in one place makes them easier to reuse and much easier to maintain later.
Replacement is another major part of the material. Finding a phone number is useful. Finding it and automatically converting it into the format you want is better. Capture groups take this one step further by allowing you to grab individual pieces of a match and rearrange them. That is how you can preserve the surrounding customer note while changing only the phone number or other matching text inside it.
The course wraps these techniques into a realistic invoice-number extraction project. The goal is not merely to find random digits, but to identify invoice references entered in several different styles while leaving unrelated dates and other numbers alone. This is the kind of messy, real-world text problem that can turn a simple import into an afternoon of frustration if you do not have the right tools.
If you are already comfortable with Access VBA and regularly work with imported data, customer notes, email text, or information copied from other systems, regular expressions belong in your toolbox. They will not solve every text problem, but for pattern-based searching, validation, extraction, and cleanup, they can save an absurd amount of repetitive string-manipulation code.
Watch the embedded video for an overview of what is covered, and visit the course page for the complete Microsoft Access Developer Level 65 training and the full implementation details.
Live long and prosper,
RR
If your fields gray out when you change a checkbox but look wrong when you open the form or move to another record, you're missing half the job. Have After Update and On Current call the same routine, so both events apply the same rule. My Gray Out Fields video shows how to put it together.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/2333.
