Computer tutorials, tips, and tricks to help you learn Microsoft Excel, Access, Word, and lots more. Visit our full site at www.ComputerLearningZone.com.
How much old VBA code is hiding in your Access database? If you have been maintaining the same application for a few years, probably more than you think. Delete a command button, rename a text box, replace a combo box, and the associated event procedure can quietly remain behind in the form or report module. It usually does not break anything, but it certainly makes your code harder to search, read, and maintain. Eventually you are digging through procedures that have not been called since the Clinton administration.
That is the problem addressed in Access Developer 61. In this class, we build an orphan VBA code detector that scans forms and reports for event procedures tied to controls that no longer exist. It is a practical database maintenance tool, but it is also a great look at a powerful side of Access VBA: using VBA to inspect the structure and code of the database itself.
Most developers are familiar with event procedures such as CustomerName_AfterUpdate or btnPrint_Click. Access creates these procedures when you attach an event to a control, and it does not necessarily remove the procedure later if that control gets deleted. The result is perfectly valid VBA that simply has no way to run anymore.
The first part of the class focuses on examining the VBA module behind a form or report. This is where things get interesting. Instead of merely running code, VBA can inspect the code module itself, identify procedures, and determine which event procedure names are present. Once you understand that capability, you can start building all kinds of useful developer utilities.
For example, an event procedure name contains valuable information. A procedure called txtLastName_AfterUpdate tells us that the control name should be txtLastName and that the event is After Update. The detector pulls apart that procedure name, then checks the form's Controls collection to see whether a control by that name is still there. If the control is gone, the procedure is a candidate for cleanup.
Of course, Click events are only the beginning. Real applications can have Before Update, After Update, Double Click, Key Down, Key Press, Key Up, Got Focus, Lost Focus, and plenty of other events. In the class, we create a table of recognized event types and use a recordset to work through them. That makes the utility far more flexible than hard-coding a few event names and hoping you remembered everything.
That table-driven approach is useful well beyond this project. Whenever you find yourself writing a long string of repetitive If statements, there is often a good chance that the information belongs in a table. It is easier to maintain, easier to expand, and much less likely to turn into one of those "why did I do this to myself?" chunks of code six months later.
Once the core detection logic is working, the class turns it into a real maintenance utility. You will build a selector that lets you scan a specific form or report when you are cleaning up one object. Then we add a Scan All option that loops through every form and report in the database, checking each module for event procedures whose corresponding controls no longer exist.
Reports are included because they can accumulate the same kind of leftovers as forms. A report might have old formatting code, click events, or other control-level procedures that survived several redesigns. If you are maintaining a large database, especially one that has been passed around between developers over the years, these little remnants can add up quickly.
The finished tool gives you a much cleaner way to audit your application. Rather than manually opening every form, reading every module, and trying to remember whether a control used to exist, you can let the scanner identify suspicious procedures for you. You still decide what to delete, of course. A good maintenance tool should report what it finds, not go on a rogue code-deletion spree while you are out getting coffee.
The bigger lesson in Developer 61 is learning how to work with Access objects programmatically. You will see how forms, reports, controls, modules, procedures, and recordsets can all work together to create tools that help maintain the database itself. Once you get comfortable with those techniques, an orphan-code detector is just one possibility. You can build code auditors, documentation tools, object inventory utilities, standards checkers, and much more.
If you have a long-running Access application and your VBA modules have started looking like an archaeological dig, this class is for you. Watch the embedded video for an overview, and visit the course page for the complete Access Developer 61 training and full implementation details.
Relationships are one of the things that keep an Access database from turning into a warp core-level mess. This quiz covers cascade updates, cascade deletes, relationship cleanup, the Relationships window, and subdatasheets. See how many you get right before checking the answers.
These are expert-level relationship questions, but the underlying ideas are important for any Access developer. Cascade delete can be useful, but it is not something you should turn on everywhere just because Access gives you a checkbox. That checkbox can delete a whole lot more than you intended.
{{YOUTUBE_EMBED_PLACEHOLDER}}
Question 1: What does cascade update related fields do in a relationship?
A. Updates every field in the related table. B. Updates matching foreign key values when the parent key changes. C. Creates a new child record whenever a parent changes. D. Changes AutoNumber values into Short Text.
Answer: B. Cascade update updates matching foreign key values in the child table when the parent key changes. For example, if you have a parent record with a manually assigned key and that key changes, Access can automatically update the matching foreign key values in related records.
In practice, I almost never use cascade update because well-designed tables usually use an AutoNumber primary key, and AutoNumber values should not be changed. Still, it can be useful occasionally when you are using a natural key or another editable value as the parent key.
Question 2: Which relationship is usually the best candidate for cascade delete related records?
A. CustomerT to OrderT, so accounting history disappears with the customer. B. EmployeeT to PayrollT, so payroll history is erased when an employee leaves. C. OrderT to OrderDetailT, so an abandoned order removes its line items. D. CustomerT to ContactT because every related table should always cascade delete.
Answer: C. An OrderT to OrderDetailT relationship is a classic example of when cascade delete can make sense. Order detail records have no real purpose without their parent order. If you delete a temporary, test, or abandoned order, deleting its line items along with it is usually exactly what you want.
On the other hand, be very careful with customers, orders, employees, payroll records, invoices, payments, and anything involving business history. Deleting a customer should not automatically erase their order history. Deleting an employee should definitely not make payroll history vanish into the Holodeck. In many cases, it is better to mark records inactive rather than delete them at all.
Question 3: What must happen before you can delete a table that participates in a saved Access relationship?
A. The relationship must be removed first, either manually or through Access's prompt. B. The table's primary key must be changed to an AutoNumber. C. Every query that uses the table must be converted to a report. D. Referential integrity must be turned on for every other table.
Answer: A. Access will not let you casually delete a table that is part of a saved relationship. You need to remove the relationship first. Depending on what you do, Access may prompt you to remove the relationship, or you can open the Relationships window and delete the relationship line yourself.
This protection is a good thing. Without it, you could delete a table and leave the rest of your database pointing into empty space.
Question 4: In the Relationships window, what happens if you remove a table box from the layout without deleting its relationship line?
A. The underlying table is deleted from the database. B. Referential integrity is automatically disabled. C. All related child records become orphaned. D. The table is only hidden from that window; the relationship still exists.
Answer: D. The Relationships window is partly a layout tool. Removing a table box from the window does not delete the actual table, disable referential integrity, or remove the relationship. It simply hides that table from the current relationship layout.
If you want to remove the actual relationship, delete the relationship line. If you just want to make the diagram less cluttered, removing the table box is perfectly fine. Think of it as cleaning up your map, not demolishing the building.
Question 5: CustomerT has related ContactT and OrderT records. You want its Datasheet view to show a custom query containing only selected contact fields as the subdatasheet. What should you configure?
A. Set CustomerT's subdatasheet name to the custom query and specify the master and child fields. B. Change CustomerT's primary key to match the query name. C. Turn on cascade delete related records for ContactT. D. Create an AutoNumber field in the custom query.
Answer: A. You can use a query as a subdatasheet instead of displaying the entire related table. Set the parent table's Subdatasheet Name property to your query, then specify the appropriate Link Master Fields and Link Child Fields.
That gives you control over what users see when they expand a customer record in Datasheet view. Instead of showing every field in ContactT, you can show only the useful contact information. Just make sure the query includes the field needed to link it back to the parent customer record.
The big takeaway is that cascade delete is best reserved for true dependent records, such as order details belonging to an order. Do not use it as a shortcut for cleaning up important historical data. Referential integrity should protect your data, not help it disappear faster.
If you want to see these relationship settings and examples in action, watch the embedded video. It covers the answers and the reasoning behind each one.
Timekeeping queries in Access can seem simple until dates and times get involved. Then somebody runs a report for a date range, notices that all of the late-afternoon shifts on the ending date are missing, and suddenly payroll looks a little suspicious. This quiz covers a few important Access date, time, totals, and overtime concepts.
Give yourself a few seconds to answer each question before checking the answer. No peeking. Access may not have a lie detector built in, but your conscience probably does.
Question 1: An employee clocks in at 8:00 AM and clocks out at 4:30 PM. In Access, subtracting Time In from Time Out returns a value measured primarily in what unit: hours, minutes, days, or seconds?
The answer is days. Access stores Date/Time values as numbers. One full day equals 1, so one hour is 1/24 of a day. If an employee works 8.5 hours, the raw subtraction result is actually a fraction of a day. To display or calculate hours, you generally multiply that result by 24.
This catches a lot of people the first time they build a timekeeping query. They subtract two times, see a decimal value such as 0.354166..., and wonder what kind of alien math Access is using. It is not alien math. It is just days.
Question 2: You have several work log records for the same employee because they clocked out for lunch. Which type of query is best for calculating that employee's total hours for the day: crosstab, aggregate, delete, or make-table?
The answer is an aggregate query, also commonly called a totals query. If an employee has multiple work periods in one day, calculate the duration of each period and then use the Sum row in the query totals to add those durations together.
A crosstab query is useful when you want to rearrange data into a spreadsheet-like summary. A delete query removes records, which is generally not the preferred payroll calculation method. A make-table query creates a new table. None of those are what you need just to total someone's daily work time.
Question 3: Your employee time query uses Between Start Date And End Date, but shifts later in the day on the end date are missing. Which criteria correctly includes the entire ending date?
The correct criteria is Greater Than or Equal To Start Date And Less Than End Date Plus 1.
In a query criteria expression, that logic looks like this: >= StartDate And < EndDate+1. The important part is using less than the day after your end date, rather than less than or equal to the end date itself.
Why? Because a date entered without a time is treated as midnight. If you search through September 6 using a criterion such as Between #9/1/2026# And #9/6/2026#, Access interprets that ending value as September 6 at 12:00:00 AM. You get records at midnight, but you miss records from 8:00 AM, 4:30 PM, or 11:59 PM that same day. Not exactly what most people mean by "include September 6."
Using a less-than comparison against the next day solves the problem neatly. It includes every possible time on the ending date, without requiring you to type 11:59:59 PM or play games with fractional seconds. This is one of the best habits you can develop when filtering date ranges in Access.
Question 4: A company pays overtime for hours worked beyond eight hours in a day. Which Access function is commonly used in a calculated query field to split regular hours from overtime hours: IIf, DLookup, Replace, or Date?
The answer is IIf, short for Immediate If. It lets you test a condition and return one value if the condition is true and another if it is false.
For example, if total hours are greater than 8, an IIf expression can return 8 regular hours. Otherwise, it returns the actual total hours worked. A similar expression can calculate overtime by returning total hours minus 8 when the employee worked more than 8 hours, and zero otherwise.
DLookup retrieves a value from another table or query, Replace works with text, and Date returns the current date. Useful tools in the right situation, sure, but they are not your go-to functions for separating regular time from overtime.
Question 5: An employee earns $20 per hour and works two overtime hours at time and a half. What should the overtime portion of the payroll calculation return: $20, $40, $60, or $80?
The answer is $60. Time and a half means the overtime rate is $30 per hour. Two overtime hours at $30 each equals $60. Yes, you do occasionally have to do regular old math when working with Access. The database has not yet figured out how to negotiate your union contract for you.
When building payroll calculations, keep the pieces separate: calculate total hours, determine regular and overtime hours, calculate the overtime rate, and then multiply. Trying to cram all of that into one giant expression can work, but it becomes much harder to troubleshoot when somebody's paycheck is off by $60.
If you missed any of these, do not worry. Date and time calculations are one of those Access topics that become much easier once you understand how Access stores values and how query criteria handle the hidden time portion of a date. Watch the embedded video for the full quiz walkthrough and a little more explanation.
Time for a quick Access and VBA calculator quiz. These are the kinds of little details that can make a form calculator work beautifully, or make it display something like 54 when you were expecting 9. Access is helpful right up until it decides your numbers are actually text. Then it gets creative.
See how many of these you can answer before reading the explanations. If you need more than a few seconds, no worries. This is not a timed final exam, and nobody is going to take away the one printer in the office that still works.
Question 1: Why can adding values from two Access text boxes sometimes produce 54 instead of 9?
Answer: The control values are being treated as text and concatenated.
If one text box contains 5 and another contains 4, VBA may see them as the strings "5" and "4" rather than the numeric values 5 and 4. When text is combined, the result is 54. That is concatenation, not arithmetic. Before performing calculations, make sure you are working with numeric values. Functions such as Val can convert appropriate text input into a number, although you should still validate the input first. Garbage in, garbage out, as they say. Or in Access terms, garbage in, mysterious runtime error at 4:57 PM.
Question 2: In VBA, which expression calculates the nth root of a positive number?
Answer: Use an exponent of 1/n. For example, the nth root of a positive value can be calculated by raising that value to the power of 1 divided by n. The caret character (^) is VBA's exponent operator.
A square root is simply a special case of this idea: the second root. So a value raised to the power of 1/2 gives its square root. Just remember that ordinary real-number roots have limitations with negative values, especially when you are dealing with even roots.
Question 3: What does On Error Resume Next do when VBA encounters a runtime error?
Answer: It ignores the error and continues with the next statement.
This can be useful in very limited, carefully controlled situations, but it is not a magic "make my program work" command. If you use it across an entire procedure, VBA can quietly skip over an important error and leave you wondering why your calculation is wrong. That is generally worse than getting an error message, because now the bug is hiding under the couch.
Question 4: What is the best way for a calculated routine to handle an attempted division by zero?
Answer: Test the divisor first, then skip the calculation and show a useful message.
Do not change the zero to one just to avoid the error. That prevents the crash, sure, but it also gives the user a mathematically incorrect answer. Instead, use a simple If Then test to see whether the divisor is zero before performing the division. If it is, explain the problem and let the user correct the input.
Question 5: A square root button uses only one input control. Which validation approach is most appropriate before converting values with Val?
Answer: Check only the input control required by the square root operation for Null.
Validate the controls that matter for the operation the user selected. There is no reason to require every text box on a calculator form to contain a value when the user only clicked the square root button. Checking unrelated controls creates unnecessary errors and frustrates users. Good validation is specific: check the needed value, make sure it is valid, then perform the calculation.
These are small concepts, but they are the foundation of building reliable calculator routines in Access VBA. Handle text-versus-number conversions correctly, validate only what you need, and prevent predictable errors before they happen. Watch the embedded video for the full quiz walkthrough and demonstrations.
Time for a little Access quiz. This one covers forms, events, bound controls, and one of those annoying little macro limitations that can catch you off guard when you try to change a toggle button caption from a standalone macro.
Grab a piece of paper, keep score if you like, and see how you do. These are the kinds of details that separate "it mostly works" from a form that behaves properly no matter which record the user is viewing.
Question 1: What happens when two bound controls on the same form use the same Yes/No field as their Control Source?
A. Only the first control can update the field. B. Each control stores a separate copy of the value. C. Both controls reflect and edit the same underlying field value. D. Access automatically converts one control into a label.
The correct answer is C. Both controls are bound to the same field in the form's record source, so they both display and edit the same value. Change one, and the other one reflects that change as Access updates the form.
This is not limited to check boxes or toggle buttons, either. The same idea applies to text boxes, combo boxes, and other bound controls. They are not separate little storage containers. They are simply different windows looking at the same field.
Question 2: Which form event is useful for updating controls whenever the user moves to a different record, including when the form first opens?
A. On Current B. On Click C. Before Insert D. On Close
The answer is On Current. The Current event fires whenever the form moves to a new current record. That includes opening the form, navigating with the record selectors, using search tools, moving through records with code, and so on.
If you need to update a caption, color, visibility setting, enabled state, or another control property based on the current record, Form Current is usually where that logic belongs. It makes sure the form gets refreshed every time the user lands on a different record, not just when they click a particular button.
Question 3: A form's On Current event and a toggle button event must both perform the same sequence of macro actions. What is the best design?
A. Copy the actions into both embedded macros. B. Put the actions in a standalone macro and use Run Macro from both events. C. Put the actions in the table's validation rule. D. Create a separate copy of the form for each event.
The right answer is B. Put the shared actions in a standalone macro, then call that macro from both events using Run Macro.
Copying the same actions into multiple embedded macros works right up until you have to change something. Then you update one copy, forget the other copy, and suddenly your form has developed two competing personalities. Keeping shared logic in one standalone macro is cleaner and easier to maintain.
Question 4: Why can the Set Property macro action fail when a standalone macro tries to change a control on a named form?
A. Set Property can only change captions, not values. B. Standalone macros cannot work with open forms. C. Set Property is limited to controls in the form containing the embedded macro. D. A form control must first be converted to an unbound control.
The correct answer is C. The Set Property macro action is designed to work with controls on the form containing the embedded macro. When you move that logic into a standalone macro, it no longer has that same form context. You cannot just point Set Property at some arbitrary control on an open form and expect it to cooperate. Access has rules, because apparently it enjoys making us earn our coffee.
A standalone macro can absolutely work with open forms. You just need to use the appropriate action for the job.
Question 5: In a standalone macro, which setup correctly assigns the literal caption "Invoice" to a toggle button named StatusToggle on an open form named OrderF?
The correct approach is to use the Set Value macro action.
Set the Item property to Forms!OrderF!StatusToggle.Caption. Then set the Expression property to "Invoice".
The quotation marks matter. Without them, Access assumes Invoice is the name of a field, control, function, or other identifier. With quotation marks, Access knows you want the literal text Invoice assigned to the toggle button's Caption property.
This is the key distinction: Set Property is convenient inside an embedded macro because Access knows which form owns the macro. For a standalone macro that needs to affect a specific open form, use Set Value and fully qualify the target control with the Forms collection, form name, control name, and property name.
So yes, a standalone macro can change a toggle button caption. Just do not try to force Set Property to do a job it was not designed to do. Use Set Value, point it to something like Forms!OrderF!StatusToggle.Caption, and give it the text you want.
If any of these questions tripped you up, watch the embedded video for the full walkthrough and demonstrations. These details are also covered in Access Advanced Level 2, Lesson 3.
You've got a Microsoft Access database that has been doing its job for years. Your staff knows how to use it, the reports come out right, the forms handle the daily work, and somewhere in that database are 10 years of VBA code, validation rules, special reports, imports, exports, and little fixes that were added because someone said, "Can we just add one checkbox?" Now you hire a couple of remote employees, and suddenly somebody says you need to throw it all away and rebuild it as a modern web application. Maybe. But probably not just because somebody put the word "modern" in a sales brochure.
The real question is not whether Access is old or whether web apps are shiny. The question is: what business problem are you trying to solve? Remote access, mobile screens, larger data volumes, stronger security, and customer self-service are all legitimate requirements. But they are different requirements, and they do not all lead to the same answer.
One of the biggest sources of confusion is that people say "Microsoft Access" when they really mean the Access database file, usually an ACCDB file. Those are related, of course, but they are not the same thing.
Access is not just a place to store tables. It is also a rapid application development environment. It gives you forms, reports, queries, macros, VBA code, automation, and all the business logic that makes your application work. Thinking of Access as nothing more than a database file is like looking at a restaurant and saying it is just a refrigerator. Sure, the refrigerator matters, but it is not the kitchen, the menu, the staff, or the cash register.
So when someone says, "You need to replace Access," the first thing you should ask is, "Which part?" Are they talking about the data storage backend? Are they talking about the forms your office staff uses all day? Or do they mean that a handful of people need a browser-based screen? Those are three very different projects, with three very different price tags.
I like to think of this as three separate decisions. First, where should the data live? Second, what should office employees use to work with that data? Third, do some users need a remote, web, or mobile interface? You can make any one of those changes without automatically changing the other two.
For example, your existing Access forms, reports, queries, and VBA can remain in place while you move the shared tables from an ACCDB backend to SQL Server. Likewise, adding a small web portal for remote salespeople does not mean you must rebuild every Access form used by accounting, customer service, and management.
That is the part a lot of "replace Access" marketing conveniently skips. They present one big red button labeled Replace Everything. Real life is usually more like remodeling a house. Maybe you need a bigger garage, but that does not mean you have to bulldoze the living room.
Another common claim is that Access cannot scale because an ACCDB file has a 2 GB size limit. Well, yes, an ACCDB file does have a practical 2 GB limit. But that is a limitation of using an ACCDB file as the data backend. It is not a limit on Access as the front-end application.
Access can connect to Microsoft SQL Server, including SQL Server hosted on your own network or in the cloud. Access uses linked tables and ODBC connections to work with SQL Server data, while SQL Server handles larger storage requirements, backups, server-level security, and more serious multi-user workloads.
This can be a very sensible upgrade path. You move the tables to SQL Server, relink the Access front end, test everything carefully, and let your existing users continue using the application they already understand. That is a major architectural improvement without forcing everyone to learn a completely new system overnight.
Access is also not limited to one person. A properly designed multi-user Access application should normally be split. Each user gets their own local copy of the front end, containing the forms, reports, queries, and VBA code. The shared data lives in a backend database.
For a small office, that backend may be an ACCDB file on a local network. As the business grows, the same application can often continue using the Access front end while the backend moves to SQL Server. An ACCDB backend is not intended for hundreds of users, advanced server security, or direct use across the public Internet, but that does not mean Access itself is a single-user product.
The phrase remote access also needs a little unpacking. If you have 20 employees happily using Access in the office and hire two remote employees, that is not automatically a reason to rebuild the entire system as a web application.
If those remote employees need the full Access application, one option is Remote Desktop or a virtual desktop environment. They can remotely operate a computer that is on your office network and run the same Access application as if they were sitting at a desk in the building. This is often much easier than trying to recreate a mature application from scratch.
Another option is to move the shared data to SQL Server and give remote users a separate Access front end tailored to what they actually need. Maybe remote salespeople only need to look up customers, update contact information, enter leads, and review orders. They may not need access to every accounting report, administration screen, and obscure monthly procedure in the main office database.
That is an important idea: different users can have different front ends while working with the same central data. Office staff can keep using the full Access application. Remote employees can use a limited Access front end, a web portal, or another tool designed specifically for their job.
What you should not do is put an ACCDB backend in Dropbox, Google Drive, OneDrive, or some other file synchronization folder and expect remote users to open it across the Internet. That is a terrific way to turn your database into a corruption experiment. Access databases are file-based, and they are not designed to have multiple users opening the same backend file over an unreliable Internet connection.
If remote users need direct access to shared data, use a proper server-based backend such as SQL Server, or use remote desktop technology so the Access application remains inside your network environment.
Mobile access is another perfectly valid reason to add something new. Access is a Windows desktop application. It does not run natively on iPhones, Android phones, or tablets. That is not a defect. It is simply what Access is designed to be.
But a mobile requirement should be specific. A warehouse employee using a phone may need to scan a barcode, change a quantity, and press Save. That is a focused mobile workflow. Build a simple mobile page or web screen with big buttons, minimal typing, and perhaps camera-based barcode scanning.
Do not try to squeeze a 140-form desktop application onto a phone just because somebody owns a phone. Most mobile users do not need the whole application. They need a few tasks performed quickly and reliably.
This is why rebuilding an old Access application can be much more expensive than it first appears. The tables are usually the easy part. Recreating a form can be straightforward too. The hard part is reproducing all the business knowledge that accumulated over the years.
That old VBA module may generate a monthly email that somebody depends on. A validation rule might prevent an expensive order-entry mistake. A query may have been refined for five years until it handles every weird exception that management forgot to document. There may be a special process accounting runs on the third Thursday of the month. Nobody remembers why it exists, but everyone knows not to remove it.
Those details are not clutter. In many cases, those details are the application.
Migration tools can help, and some of them are quite good. But if a vendor says they can automatically convert 80 percent of your Access application, ask them which 80 percent. The remaining 20 percent may contain 80 percent of the complexity, testing, exceptions, and expensive surprises.
There are certainly times when a web application is the right answer. If you have hundreds or thousands of geographically distributed users who need browser access, a web platform may make perfect sense. If you have outside customers who need self-service accounts, online ordering, and access from anywhere, a customer-facing web application is probably a better fit.
If nearly everyone uses a phone or tablet as their primary device, then a mobile-first system may be the correct direction. And if your organization has a development team, security policies, hosting infrastructure, and internal standards built around .NET, Power Platform, or another platform, those are valid business reasons to choose that platform.
But "Access is old" is not a business requirement. "The web is newer" is not a business requirement either. Windows is old too, and nobody is suggesting we all go back to typewriters.
For many organizations, the best approach is evolution instead of revolution. Split the Access database properly. Keep a local front end for each user. Move the backend tables to SQL Server when data size, user count, security, or remote connectivity makes that appropriate. Then add targeted web, mobile, dashboard, or customer portal features only where they are actually needed.
Once your data lives in a proper server database such as SQL Server, you have options. Access can continue using the data. A web application can use it. A mobile app can use it. Dashboards, reporting tools, ASP.NET applications, PHP applications, and other systems can use it too. You are no longer stuck with one interface, and you do not have to destroy a working one just to add another.
So before committing to a major rewrite, take a step back. Identify who needs access, what device they use, what tasks they actually perform, how much data you have, and what security or administration requirements have changed. Then choose the architecture that solves those problems with the least unnecessary disruption.
Sometimes the right answer really is to replace the Access front end with a web application. Sometimes the right answer is SQL Server plus Access. Sometimes it is a small customer portal, a warehouse scanning app, or remote desktop for a couple of employees. The right answer depends on the job, not on whoever is trying to sell you the biggest toolbox.
Watch the embedded video for the full discussion and practical examples of how Access, SQL Server, web applications, and remote users can work together without throwing away a perfectly good application.
Timekeeping queries in Access can seem simple until dates and times get involved. Then somebody runs a report for a date range, notices that all of the late-afternoon shifts on the ending date are missing, and suddenly payroll looks a little suspicious. This quiz covers a few important Access date, time, totals, and overtime concepts.
Give yourself a few seconds to answer each question before checking the answer. No peeking. Access may not have a lie detector built in, but your conscience probably does.
Question 1: An employee clocks in at 8:00 AM and clocks out at 4:30 PM. In Access, subtracting Time In from Time Out returns a value measured primarily in what unit: hours, minutes, days, or seconds?
The answer is days. Access stores Date/Time values as numbers. One full day equals 1, so one hour is 1/24 of a day. If an employee works 8.5 hours, the raw subtraction result is actually a fraction of a day. To display or calculate hours, you generally multiply that result by 24.
This catches a lot of people the first time they build a timekeeping query. They subtract two times, see a decimal value such as 0.354166..., and wonder what kind of alien math Access is using. It is not alien math. It is just days.
Question 2: You have several work log records for the same employee because they clocked out for lunch. Which type of query is best for calculating that employee's total hours for the day: crosstab, aggregate, delete, or make-table?
The answer is an aggregate query, also commonly called a totals query. If an employee has multiple work periods in one day, calculate the duration of each period and then use the Sum row in the query totals to add those durations together.
A crosstab query is useful when you want to rearrange data into a spreadsheet-like summary. A delete query removes records, which is generally not the preferred payroll calculation method. A make-table query creates a new table. None of those are what you need just to total someone's daily work time.
Question 3: Your employee time query uses Between Start Date And End Date, but shifts later in the day on the end date are missing. Which criteria correctly includes the entire ending date?
The correct criteria is Greater Than or Equal To Start Date And Less Than End Date Plus 1.
In a query criteria expression, that logic looks like this: >= StartDate And < EndDate+1. The important part is using less than the day after your end date, rather than less than or equal to the end date itself.
Why? Because a date entered without a time is treated as midnight. If you search through September 6 using a criterion such as Between #9/1/2026# And #9/6/2026#, Access interprets that ending value as September 6 at 12:00:00 AM. You get records at midnight, but you miss records from 8:00 AM, 4:30 PM, or 11:59 PM that same day. Not exactly what most people mean by "include September 6."
Using a less-than comparison against the next day solves the problem neatly. It includes every possible time on the ending date, without requiring you to type 11:59:59 PM or play games with fractional seconds. This is one of the best habits you can develop when filtering date ranges in Access.
Question 4: A company pays overtime for hours worked beyond eight hours in a day. Which Access function is commonly used in a calculated query field to split regular hours from overtime hours: IIf, DLookup, Replace, or Date?
The answer is IIf, short for Immediate If. It lets you test a condition and return one value if the condition is true and another if it is false.
For example, if total hours are greater than 8, an IIf expression can return 8 regular hours. Otherwise, it returns the actual total hours worked. A similar expression can calculate overtime by returning total hours minus 8 when the employee worked more than 8 hours, and zero otherwise.
DLookup retrieves a value from another table or query, Replace works with text, and Date returns the current date. Useful tools in the right situation, sure, but they are not your go-to functions for separating regular time from overtime.
Question 5: An employee earns $20 per hour and works two overtime hours at time and a half. What should the overtime portion of the payroll calculation return: $20, $40, $60, or $80?
The answer is $60. Time and a half means the overtime rate is $30 per hour. Two overtime hours at $30 each equals $60. Yes, you do occasionally have to do regular old math when working with Access. The database has not yet figured out how to negotiate your union contract for you.
When building payroll calculations, keep the pieces separate: calculate total hours, determine regular and overtime hours, calculate the overtime rate, and then multiply. Trying to cram all of that into one giant expression can work, but it becomes much harder to troubleshoot when somebody's paycheck is off by $60.
If you missed any of these, do not worry. Date and time calculations are one of those Access topics that become much easier once you understand how Access stores values and how query criteria handle the hidden time portion of a date. Watch the embedded video for the full quiz walkthrough and a little more explanation.
