Getting different counts from the same table? Check which field you are counting. An AutoNumber customer ID gives you a count of every customer, while Count([State]) skips Null values in State. In this video, I show you how to set up a totals query and get useful counts from your data.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/4432.
Got a date/time field but only need the time? TimeValue pulls out that portion so you can compare appointment hours without the date getting in the way. In this Fast Tips video, I'll show you how it works and how to use it in a validation rule for appointments between 9 AM and 5 PM.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/2485.
If you search for programming help and keep finding VB, VBA, VB.NET, VBScript, and VB6, you are not alone in wondering whether they are all the same thing. They are related, and the syntax can look very similar, but they are absolutely not interchangeable. The important question is not which one has the newest-sounding name. The important question is: where is your code supposed to run?
If you are writing code in Microsoft Access, the answer is easy: you are learning VBA, which stands for Visual Basic for Applications. That distinction matters because copying code written for VB.NET, VB6, or VBScript into an Access module can lead to a whole lot of "Why doesn't this work?" and possibly some colorful language.
Let's start with the old favorite: Classic Visual Basic, usually meaning VB6. Visual Basic 6 was a standalone Windows application development environment. You could create forms, drop controls onto them, add buttons, and write event-driven code behind those buttons.
That should sound familiar to Access developers. Access forms work in a similar event-driven way. You put controls on a form and write code that runs when someone clicks a button, changes a value, opens a form, and so on. The big difference is that VB6 was used to create separate Windows programs. An Access form lives inside an Access database and depends on Access being there.
VB6 is old now. Microsoft ended mainstream support for the VB6 development environment a long time ago, although the core VB6 runtime still has limited support on supported versions of Windows. That means many old VB6 applications still run, especially in businesses that have been using the same custom software for years. But maintaining an old VB6 application is very different from building a modern .NET application.
VBA, on the other hand, is the language built into Office applications. In Access, VBA lets you work with forms, reports, controls, queries, recordsets, tables, macros, and the rest of the Access object model. In Excel, VBA works with workbooks, worksheets, ranges, charts, and cells. In Word, it works with documents, paragraphs, bookmarks, and so on.
The language is similar across Office applications, but the objects are not. An Excel VBA example that refers to Worksheets("Sheet1") will not magically work in Access, because Access does not have worksheets. Likewise, Access code that refers to a form or report will not run in Excel without being completely reworked for that environment.
This is one of the biggest beginner mistakes: finding something called "Visual Basic code" online and assuming it applies to Access. Before using any example, first figure out what environment it was written for. If you are programming an Access database, search for Access VBA specifically whenever possible.
VBA is designed to extend the application you are already using. You are not normally building an entirely separate Windows program from scratch. You are automating and enhancing Access, Excel, Word, Outlook, PowerPoint, or another host application. That is why it is called Visual Basic for Applications.
Then there is VB.NET, which Microsoft often calls simply Visual Basic now, because apparently having several things named Visual Basic was not confusing enough.
VB.NET is the modern member of the Visual Basic family. It is used with the .NET platform and development tools such as Visual Studio. You can build Windows applications, web-related services, libraries, utilities, and other .NET projects with it. You may see visual form designers, buttons, text boxes, and event procedures that look somewhat like VB6 or Access forms, but do not let that fool you.
VB.NET is not VB6 with a fresh coat of paint. It uses a different runtime, different project structure, different libraries, different object model, and different programming features. Some basic syntax may look familiar, such as If...Then, loops, variables, and procedures, but an Access VBA procedure generally cannot just be pasted into a VB.NET project and expected to run.
The same goes in reverse. A VB.NET example might use .NET classes, namespaces, Windows Forms controls, or file-handling methods that do not exist in Access VBA. You may be able to learn programming concepts from it, but you still have to translate the solution into the Access environment.
Finally, there is VBScript. VBScript was a lightweight scripting language related to Visual Basic. It has commonly been used in old Windows automation scripts, Classic ASP web pages, login scripts, and administrative utilities. A typical VBScript file has a .vbs extension and can be run by Windows Script Host using WScript or CScript.
VBScript can work with COM objects and automate applications, which is why it was useful for lots of small jobs over the years. But it is not the same as VBA. It does not run inside your Access database as a normal VBA module, and it does not provide the same development environment, references, debugging tools, or Access object model that you have in Access.
VBScript has also been deprecated by Microsoft and is being phased out of Windows over time. It has not simply vanished overnight, and many existing scripts can still run depending on the Windows version and configuration. But if you maintain old VBS files, you should know what runs them and start thinking about their long-term future. Old scripts have a way of becoming "critical business infrastructure" right before somebody discovers they are 20 years old.
So here is the practical cheat sheet. If your code is behind Access forms, reports, buttons, queries, or recordsets, you are using Access VBA. If you are maintaining an older standalone Windows application, it may be VB6. If you are working in Visual Studio on a modern .NET project, you are dealing with VB.NET. And if you inherited a pile of .vbs files, that is VBScript.
They are all members of the larger Visual Basic family, and knowing one can certainly help you recognize pieces of another. Variables, conditions, loops, functions, and event procedures often feel familiar. But the runtime, available libraries, objects, project files, and deployment methods matter just as much as the syntax.
When looking for help, start with the application you are using. Search for "Access VBA" if you are working in Access. Search for "Excel VBA" if you are working in Excel. Search for "VB.NET" if you are working in Visual Studio. That one small habit will save you a lot of time and keep you from trying to use a hammer when what you found online is clearly instructions for a nail gun.
If you are new to programming in Access, the embedded video includes a walkthrough of these distinctions and will help you get your bearings. Once you know that Access uses VBA, you will have a much easier time finding examples, tutorials, and answers that actually apply to your database.
Live long and prosper,
RR
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.
