Thursday, November 19, 2026

Odd or Even? Check the Remainder

Need to tell odd and even numbers apart in an Access query? Check the remainder, not the last digit. CustomerID Mod 2 equals zero for even numbers. Wrap that test in IIf to display Even or Odd. Watch the video for the query setup.

Odd or Even? Check the Remainder

For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/Mod.

Wednesday, November 18, 2026

What Records Control Report Detail Section Printing in Microsoft Access? Video Quiz A4.2

Ready for a quick Microsoft Access report quiz? This one covers a deceptively important idea: what actually controls how many times a report's Detail section prints. If you have ever built mailing labels, invoices, packing slips, or any report that mysteriously prints too many or too few rows, this is the stuff you need to know.

Grab a piece of paper, keep score if you like, and do not peek at the answers too quickly. There are five questions, all focused on Access reports, record sources, form references, and printing a full sheet of labels.

Question 1: What primarily determines how many times a report's Detail section prints?

A. The number of text boxes in the Detail section
B. The number of records returned by the report's Record Source
C. The number of pages selected in Print Preview
D. The number of controls on the form that opened it

Answer: B. The number of records returned by the report's Record Source.

In a normal Access report, each record returned by the report's Record Source gets one pass through the Detail section. If the report's query returns 13 records, the Detail section prints 13 times. If it returns 30 records, you get 30 detail positions. The number of text boxes and labels you put in the Detail section has nothing to do with the number of records Access processes.

This is the key idea behind many label-report tricks. Need 30 labels on a sheet? Give the report 30 records to process. Access does not care whether those records contain useful business data or are simply there to make the Detail section repeat.

Question 2: A report control uses an expression that refers to a control on an open form. What must be true when the report runs?

A. The form must remain open so Access can evaluate the reference
B. The form must be saved as a report first
C. The report and form must use the same Record Source
D. The referenced form control must be unbound

Answer: A. The form must remain open so Access can evaluate the reference.

If a report control contains an expression that reaches out to a form, Access has to be able to find that form at the moment the report is generated. A reference such as =Forms!CustomerF!FirstName reads the value from an open form named CustomerF.

If CustomerF is closed, Access cannot resolve that reference. You will generally get an error or an ugly little #Name? instead of the value you expected. The usual solution is to open the report from the form and leave the form open while the report is previewed or printed.

Question 3: Why might a label report use a small table containing exactly 30 otherwise meaningless records?

A. To permanently store each customer's mailing address
B. To make Access automatically create 30 new customers
C. To supply 30 Detail-section records for one full sheet
D. To force the report to use landscape orientation

Answer: C. To supply 30 Detail-section records for one full sheet.

This is a classic Access technique. You create a little helper table, sometimes called a numbers table, with 30 records. Those records can be nothing more than ID values from 1 through 30. The report uses that table as its Record Source, and Access prints the Detail section once for each record.

The labels themselves can contain calculated controls that read values from the currently open customer form. Since the report has 30 source records, Access produces 30 label positions, each showing the same customer's information. The table is not storing customer data. It is just giving Access a reason to repeat the Detail section 30 times. Think of it as a row generator. Not glamorous, but it gets the job done.

Question 4: Which Control Source expression correctly displays the FirstName control from an open form named CustomerF in a report text box?

A. [CustomerF].[FirstName]
B. =[CustomerF]![FirstName]
C. =Forms!CustomerF!FirstName
D. =Reports!CustomerF!FirstName

Answer: C. =Forms!CustomerF!FirstName

The Forms collection tells Access that you want to retrieve something from an open form. The first exclamation point identifies the form, and the second identifies the control on that form. The equals sign is important too, because it tells Access this is an expression to evaluate, not literal text to print.

You will use expressions like this when the report's Record Source is not the customer table itself. For example, your label report might use the 30-record helper table as its source, while each label reaches over to the open customer form to display the customer's name and address.

Question 5: A full-sheet label report repeats values from the current customer form 30 times. Why is it a poor choice when three labels need different text?

A. Reports cannot contain calculated controls
B. Changing the form value changes every repeated label that reads it
C. Access reports can print only one copy of each record
D. A report cannot use a table with 30 records

Answer: B. Changing the form value changes every repeated label that reads it.

If all 30 labels use expressions that point to the same form controls, they all evaluate the same values. Change the customer's FirstName on the form, and every label using that expression changes right along with it. That is perfect when you want a full sheet of identical labels. It is not so great when label 1 needs one message, label 2 needs another, and label 3 needs something completely different.

For individually editable labels, you need separate stored records for the individual labels, or some other process that creates separate data for each one. A report can certainly display calculated controls, and it can absolutely use a 30-record helper table. The problem is simply that all the repeated labels are reading the same source values.

So, how did you do? The big takeaway is that a report's Detail section is driven by its Record Source. Once you understand that, tricks like full-sheet label printing make a lot more sense, and Access stops looking quite so much like it is operating a wormhole behind your back.

If you want to see these report and label concepts in action, watch the embedded video. These topics are also covered in more depth in Microsoft Access Advanced Level 4.

Live long and prosper,
RR

Build a Date from Its Parts

Got a year in one field and a month in another? DateSerial puts the pieces together into a real date value. The order is year, month, day. If you only recorded the year and month, using day 1 is an assumption, not a recovered date. In this video, I show you how to build the dates, handle missing source values, and use an update query to save the results. Back up your database first.

Build a Date from Its Parts

For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/DateSerial.

Tuesday, November 17, 2026

Can Your Software Survive Without You?

If you disappeared for a week, could the people using your database keep the business running without you? Or would your phone start ringing the moment you got comfortable on the beach? That is one of the best tests you can give any business application: can ordinary day-to-day work continue when the developer is unavailable?

A database is not finished just because it works on your computer while you are sitting there to explain every button, repair every bad import, and answer every "what do I click next?" question. Good software should make people's jobs easier. It should not turn the developer into a permanent member of the staff, on call forever.

Back when I was doing consulting work, I would often walk into businesses with information scattered all over the place. There might be Excel spreadsheets, Word documents, paper forms, and sometimes an old DOS program that only one employee understood. Yes, DOS. I am old.

Building an Access database meant learning how that business actually operated, bringing those disconnected pieces together, and giving the employees a system they could use. But the job was not done when the database technically worked. It was done when the people using it could work confidently without me standing behind them.

That leads to a simple design principle: build software that explains itself. Buttons should say what they do. A caption like "Run Monthly Sales Report" is much better than "Button 17." Use Control Tip Text when it helps. Put short instructions directly on forms where users need them. If a process is complicated, include a Help button that opens information relevant to that specific screen.

Do not expect someone to remember a six-step procedure they only perform once a month. If the database needs a monthly import, report generation, archive process, or other occasional task, guide the user through it. Better yet, automate as much of it as you reasonably can.

Error messages are another big one. A message such as "Error 3022" may mean something to an experienced Access developer, but it means absolutely nothing useful to the person trying to enter a customer order. Tell the user what happened in plain English, what it probably means, and what they should do next. For example, explain that they entered a duplicate value where a unique value is required, and tell them which record or field needs attention.

One of the best ways to find weak spots in an application is to sit down with someone while they use it. Then keep your mouth shut. Let them perform their regular tasks without coaching them through every screen. Every time you feel the urge to say, "Before you do that, you need to..." you have probably found something that needs to be improved.

Maybe a button is confusing. Maybe instructions are missing. Maybe the form should automatically fill in a value, verify a condition, or prevent the user from taking an invalid step in the first place. If you have explained the same thing to three different people, the odds are good that the problem is not those three people. The interface is trying to tell you something.

Another common mistake is building a separate report for every question a customer asks. At first, that seems reasonable. They want a report for this month, then another one for last month, then one filtered by department, employee, customer, or date range. Before long, you have a database full of nearly identical reports and a client who still needs to call you whenever they think of a new variation.

Whenever it makes sense, build tools instead of just one-off reports. Give users a form where they can choose their own date range, customer, department, status, or whatever criteria are appropriate. A flexible reporting form can answer dozens of routine questions without requiring dozens of report objects and a support call every few months.

Sure, if you bill by the hour, repeat calls for tiny changes may sound like job security. I always preferred building a better system up front. The customer gets software that can grow with the business, and you get fewer interruptions when you are trying to enjoy your vacation. Everybody wins.

Even a well-designed database needs some documentation, but that does not mean you need to write a 300-page manual that nobody will read. Start with the practical information people need: which file they should open, where the data is stored, what must happen before monthly reports are generated, and what an import process does.

Pay particular attention to processes that can cause trouble when repeated. If running an import twice could duplicate records, users need to know that. Better yet, design the process so it can detect whether it already ran and protect users from making that mistake. A warning is helpful. Prevention is better.

For your own development notes, keep track of important objects, business rules, unusual design decisions, and anything a future developer would need to know. A simple spreadsheet is often enough. The documentation for users, however, is usually most useful when it is built directly into the application, right where they need it.

Also, every captain needs a number one. If you are the only person in the organization who understands the database, that is a risk. Find someone who is reasonably comfortable with computers and teach them the basics. They do not need to become an Access developer or learn VBA. They should simply know how to handle routine issues, confirm that regular processes have completed, locate basic instructions, and recognize when a problem really does need technical help.

This becomes especially important if you built the system and you maintain it too. What happens if you get sick? What if you have an emergency? What if you decide to extend your vacation because a couple of Ferengi talked you into investing in beachfront property? The business still has to function.

In my own business, I have systems that need to keep operating whether I am at my desk or away from the office. That means thinking about automation, error handling, logging, backups, and notifications. When something goes wrong, the system should either recover gracefully or provide enough useful information that I can understand what happened without remotely babysitting it from a hotel room.

You cannot anticipate every possible problem. Hardware fails. Networks go down. Users will always discover creative new ways to break things. But you can eliminate a surprising number of support calls by asking a few questions while you are building the application.

Can users recover from common mistakes? Can they tell what a button does? Can they get the information they need without asking you to modify a query? Does someone get notified when an automated task fails? Is there a reliable backup plan? These are design questions, not afterthoughts to consider while you are packing your suitcase.

Although I am talking primarily about Microsoft Access databases here, the same idea applies to Excel automation, SQL Server applications, websites, and just about any custom business software. The goal is not merely to build something clever. The goal is to build something understandable, dependable, and useful to the people who rely on it.

Try the seven-day test before your next vacation. Pretend you will be completely unreachable for a full week. No phone calls, no email, no remote desktop sessions from the hotel. Can the office keep doing its job? Will routine operations continue? Does someone know what to do when something unexpected happens?

If the answer is no, that is not necessarily bad news. It is a very useful list of improvements waiting to be made. A database that works while you are there supervising it is one thing. A database that keeps the business moving while you are relaxing on a beach is something else entirely.

Watch the embedded video for more thoughts and real-world examples. Then ask yourself a simple question: if you disappeared tomorrow, what would your users get stuck on first?

Live long and prosper,
RR

Find Each Share of the Total

Want to see what share of the total each category contributes? Divide its amount by the grand total with DSum, then display that fraction as a percent. Watch the video for the query-to-chart walkthrough.

Find Each Share of the Total

For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/1448.

Monday, November 16, 2026

Find the Last Backslash with InStrRev

Need just the filename from a full path? InStrRev finds the last backslash, but its answer is still a position counted from the left. Subtract that position from the full length, then use Right to keep the filename. Watch the video for the query walkthrough.

Find the Last Backslash with InStrRev

For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/InStrRev.

How Do You Calculate Total Inventory Value in Microsoft Access Forms? Video Quiz X3.4

Ready for a quick Microsoft Access totals quiz? If you work with inventory, orders, invoices, or anything involving numbers, form footer totals and aggregate functions are things you should know cold. Otherwise, Access will happily calculate something, just not necessarily the thing you meant.

This quiz covers five common questions about totals in continuous forms and subforms. See how many you can answer before checking the explanations. No peeking. Bigfoot is watching.

Question 1: You have a continuous form showing several products and their quantities. Where should you place a calculated text box if you want to display the total quantity underneath all of the records?

The answer is the Form Footer section. The Detail section repeats once for every record, so putting a grand total there would repeat the same total on every row. The form footer appears once at the bottom of the form, which makes it the natural home for totals, counts, averages, and similar summary values.

Question 2: Which control source expression adds all values in a field named QuantityOnHand?

The correct function is Sum. In a form footer text box, you would use an expression such as =Sum([QuantityOnHand]). Access evaluates the field across the records shown in the form and displays the total.

Question 3: A subform contains three product records with quantities of 4, 6, and 10. What does =Count(*) display in the form footer?

The answer is 3. Count does not add the quantities. It counts records. Since there are three rows in the subform, Count(*) returns three. This is useful when you want to know how many items, orders, customers, or detail records are currently displayed.

Question 4: A continuous form displays a different unit price for each product. Which aggregate function reliably returns the highest unit price?

That is Max. Sum would add every price together, Average would calculate the average price, and Last is not a safe way to determine the largest value because "last" depends on the form's record order. Max is specifically designed to return the highest value.

Question 5: You have a subform listing products with UnitPrice and QuantityOnHand fields. How do you calculate the total inventory value?

The correct approach is to multiply price by quantity for each record, then sum those results. Adding up unit prices alone tells you almost nothing. Counting quantities tells you how many units you have, but not what they are worth. Each product's inventory value is its unit price times the number of units on hand. The grand total is the sum of those individual product values.

This is a very common Access calculation, and it is worth getting right. Think of it this way: if one item costs $10 and you have 5 of them, that row is worth $50. If another item costs $2 and you have 100, that row is worth $200. Add the row values together to get the actual value of your inventory. Simple math, but it is easy to accidentally sum the wrong field and get a number that looks official enough to fool everybody.

Form footers are excellent for these kinds of live summary calculations, especially in continuous forms and subforms. Just remember the basic distinction: Sum adds values, Count counts records, Max finds the highest value, and calculated totals often need to perform row-level math before the final sum.

If you want a quick walkthrough of these questions, watch the embedded quiz video. These topics are also covered in more depth in Microsoft Access Expert Level 3 Lesson 4, where we dig into form footer totals and useful aggregate calculations.

Live long and prosper,
RR