Sunday, November 15, 2026

First True Wins with Switch

More than one Switch test can be True. Access returns the value paired with the first True test, so put your conditions in the right order. A family size of 4 is both less than 5 and less than 10, but this expression returns Small. Watch the video to build the query.

First True Wins with Switch

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

Find Who Has No Match

Which customers don't have any contact records? Start with a left join so those customers aren't excluded, then use Is Null on the related table's CustomerID field to keep only the unmatched ones. In Access Expert 18, Lesson 5, I show you how to build a Find Unmatched query with the wizard and in Query Design.

Find Who Has No Match

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

Latest for This Customer

Want to see a customer's last order date without opening the order form? DMax can find the most recent date in OrderT. Add criteria to limit it to one customer. Without that criteria, you're asking for the latest date across all customers. My DMax video shows how to put the result right on your customer form.

Latest for This Customer

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

Saturday, November 14, 2026

Why Your Microsoft Access Combo Box Selects the Wrong Record After a UNION Query

A combo box that appears to select the wrong record can be one of those wonderfully frustrating Access problems. You click a customer, employee, food, meal, or whatever else is in your list, and Access displays a completely different item. The list itself looks fine. The names are all there. So naturally, you start suspecting the combo box, the form, the query, Mercury being in retrograde... something. In many cases, the real problem is much simpler: two different tables happen to contain the same numeric ID.

This usually comes up after creating a UNION query to combine records from separate tables. For example, maybe you want one combo box that lets the user choose either an employee or a customer. Or perhaps you have foods and saved meals in one picker. A UNION query is a perfectly reasonable way to build that combined list. The trouble begins when both tables use AutoNumber IDs and the combo box is bound only to that number.

Remember, an AutoNumber is unique within its own table. EmployeeID 15 is unique in the Employee table. CustomerID 15 is unique in the Customer table. But Access does not promise that EmployeeID 15 and CustomerID 15 refer to the same person. In fact, they almost certainly do not.

Suppose your Employee table contains Alex Morgan with EmployeeID 15, and your Customer table contains Sam Rivera with CustomerID 15. When you combine those records with a UNION query, both names appear in the list, which is correct. The names are different, so the UNION query keeps both rows.

But if the first, hidden, bound column in your combo box is just the numeric ID, then both Alex and Sam have the same combo box value: 15. As far as the combo box is concerned, those are not two reliably distinct selections anymore.

This is why the problem can look so strange. You select Sam Rivera, and the combo box stores 15. When Access goes looking for the row associated with 15, it may find Alex Morgan first and display that row instead. Nothing supernatural is happening. The combo box is doing exactly what you told it to do, even if what you told it to do was a little ambiguous.

The fix is to give the combined list a temporary value that is unique across both sources. The easiest way is usually to add a prefix that identifies where the record came from. Employees can use values such as E15, E16, and E17. Customers can use C15, C16, and C17.

Now EmployeeID 15 and CustomerID 15 are no longer competing for the same value. The combo box sees E15 and C15, which are clearly different. The user can still see a friendly name such as "Alex Morgan" or "Sam Rivera." The prefix is primarily for the hidden bound column, not necessarily something the user needs to see.

You are not changing the actual AutoNumber fields in either table. Leave those alone. They are doing their jobs just fine. You are simply creating a unique selector value in the UNION query so that the combo box can tell which table and which record the selected row belongs to.

In a UNION query, the employee side might construct a selector by concatenating the letter E with the employee's ID. The customer side does the same thing with C and the customer ID. The important part is that the first column used as the combo box's bound value contains the prefixed identifier, rather than the raw number alone.

I ran into this exact issue in my fitness database when combining food items and saved meals into one combo box. A food and a meal could both have the same numeric ID. If the combo box stored only that number, selecting a meal could make the combo box jump to a food item with the same ID. Prefixing the values as F1 and M1 fixed it immediately.

There is another useful benefit to this approach. Once you have the selected value, the prefix tells your VBA code what kind of record was selected. If the value starts with F, you know it is a food item and can process it one way. If it starts with M, you know it is a saved meal and can process it differently. That is much safer than trying to guess based on a description field or some other text that a user might later edit.

You could use other methods to make the values unique. Some developers add a large offset to one table's IDs, for example. That can work, but prefixes are usually easier to read and easier to troubleshoot later. Seeing C15 immediately tells you more than seeing 1000015. Future You will appreciate the hint.

Just make sure you think through what happens after the user makes a selection. If your form or VBA code needs to open a record, add related records, or otherwise act on the selected value, it must interpret the prefix and use the correct underlying table. The selector value solves the collision problem, but it is not a replacement for the original numeric primary key.

So the next time a UNION-based combo box seems to pick the wrong record, inspect the bound column first. If two different source tables can produce the same value there, you have found the bug. Give each source a distinct prefix, bind the combo box to that new selector field, and Access will finally know exactly what you meant.

The embedded video includes a full demonstration of the problem and the fix in a real Access database. It is a small design detail, but it can save you from one of those maddening bugs that makes a database look haunted.

Live long and prosper,
RR

Yes Is Minus One

Wondering why your checked boxes add up to a negative number? Access stores Yes as -1 and No as 0. To count the checked records in a form footer, use =Sum([IsActive]*-1), replacing IsActive with your Yes/No field. Two checked records and one unchecked record give you 2. My Yes/No Fields video shows the different ways to display these values and total them.

Yes Is Minus One

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

Hide Optional Text with Null

Ever get "Joe and Smith" when a spouse name is missing? Group the optional " and " text with the spouse field using +, then use & to join the rest. When Spouse is Null, the optional phrase becomes Null too, leaving "Joe Smith". This example uses text fields; an empty string is not Null. My Null Math video shows how this works in greeting lines and combined names.

Hide Optional Text with Null

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

Why Computers Get Math Wrong: The Floating Point Problem

You have probably seen it before: type 0.1 + 0.2 into a programming language and, instead of getting a clean 0.3, you get something like 0.30000000000000004. It looks ridiculous. We have computers that can render movies, guide spacecraft, and predict the weather, but apparently they cannot add two little decimal numbers without falling apart. The good news is that the computer is not broken. It is just working with an approximation of the numbers you gave it.

The important thing to understand is that most computers do not store ordinary decimal values internally. They use binary, which is built from 0s and 1s. Some numbers work perfectly in binary, while others do not. Once you understand that little compromise, a lot of supposedly strange computer behavior starts making sense.

In decimal, we are used to place values like tenths, hundredths, and thousandths. In binary, the places after the binary point are halves, quarters, eighths, sixteenths, and so on. A value like 0.5 is easy in binary because it is exactly one half. So is 0.25, which is one quarter.

But 0.1 is a different story. A tenth cannot be represented exactly using a finite number of binary digits. It repeats forever, much like one third repeats forever in decimal. We can write 1 divided by 3 as 0.333333..., but eventually we have to stop writing digits. Computers have the same problem with certain values in binary, except they have to stop because storage space is finite. Even on the expensive computers.

When the binary representation goes on forever, the computer stores the nearest value it can fit into its available space. That stored value is extremely close to 0.1, but it is not exactly 0.1. In the usual floating-point format, the approximation for 0.1 is slightly larger than an exact tenth. The difference is so tiny that it normally does not matter, and most software politely displays it simply as 0.1.

That display is the first thing that trips people up. Seeing 0.1 on the screen does not necessarily mean the computer has stored an exact decimal tenth. It may just be showing you a nice, short, human-friendly version of a nearby binary value. Formatting is presentation. It is not magic surgery on the number underneath.

Now add the stored approximation of 0.1 to the stored approximation of 0.2. The answer is also rounded to fit into the floating-point format. When the result gets converted back into decimal for display, you may see 0.30000000000000004. That extra 4 is not a bug, a prank, or a tiny gremlin hiding in your CPU. It is simply the decimal description of the closest binary value the computer ended up with.

This becomes important when programmers compare values. A test such as 0.1 + 0.2 = 0.3 can return False in many languages because exact equality means the stored values must be identical bit for bit. They may be incredibly close, but close is not the same as identical to a computer.

For scientific measurements, graphics, engineering work, and similar jobs, exact equality is often the wrong question anyway. The better question is usually, "Are these values close enough for what I am doing?" That requires a tolerance, and the tolerance should match the job. A carpenter, a laboratory technician, and someone calculating orbital mechanics should not all use the same margin of error. One magic tiny number is not the answer.

Floating point is useful because it provides a huge range of values with a limited number of bits. Most modern systems use a standard called IEEE 754, which has helped make floating-point behavior much more predictable across different computers. A common format is called binary 64, often referred to as a Double. It provides roughly 15 to 16 significant decimal digits of precision and an enormous range of possible values.

That does not mean every number has the same spacing between representable values. Think of floating point as a ruler whose marks spread farther apart as the numbers get larger. Near zero, the marks are incredibly close together. Far away from zero, the gaps become wider. Eventually, you can make a number so large that adding 1 to it changes nothing because 1 is too small to reach the next available floating-point value. The 1 did not disappear mathematically. The ruler just was not fine enough to measure it.

The order in which you perform calculations can matter too. In ordinary arithmetic, changing the grouping of additions should not change the answer. With floating point, rounded intermediate results can make it matter. If you add a tiny number to a huge number, the tiny number may get lost. If you first cancel two huge values and then add the small number, it may survive. Same ingredients, different order, different result. Mathematics is still innocent here. Finite precision is the culprit.

Subtraction can cause another problem when you subtract two nearly equal values. Their leading digits cancel, leaving a very small result. Any tiny inaccuracies already present in the original values can suddenly become a large part of that remaining answer. This is why numerical specialists care about the formula itself, not just how many digits the computer supports. Sometimes rearranging a calculation is more helpful than throwing more precision at it and hoping for the best.

It is worth separating normal floating-point behavior from actual hardware errors. In 1994, the early Pentium processor had a real division flaw that caused incorrect results in some cases. That was a defect in the processor's arithmetic implementation. The 0.1 + 0.2 issue, on the other hand, can happen on a perfectly healthy computer doing exactly what its number format requires.

Finite precision can become serious when a system is poorly designed around it. The Patriot missile failure in 1991 is a sobering example. A timing calculation based on tenths of a second accumulated a precision error after the system had operated continuously for many hours. That timing error affected where the system looked for an incoming Scud missile. This was not the same format or the same situation as a modern Double value, but it demonstrates an important lesson: small numerical assumptions can become big problems when they accumulate over time.

In Excel, you may encounter similar surprises. Microsoft documents examples where calculations that look like they should produce a clean decimal result reveal a tiny leftover difference when enough decimal places are shown. If you format a cell to display two decimal places, Excel usually changes only what you see. The full stored value remains underneath, ready to participate in later calculations.

Two cells can both look like they contain 0.90 and still behave slightly differently in a formula. That is why formatting should not be mistaken for rounding the stored value. Excel's Precision as displayed option goes further and actually changes stored values to match their displayed precision, but Microsoft warns that doing so can permanently lose accuracy. Do not flip that switch just to make an ugly decimal tail go away unless you understand the consequences and have a backup.

For database users, data types matter just as much. In Microsoft Access, a Double field uses floating-point arithmetic. A Currency field uses a scaled integer value with four decimal places, making it a better fit for many financial calculations. Formatting a Double field as Currency merely adds dollar signs and commas to the display. It does not transform the underlying floating-point value into the Currency data type. A dollar sign is decoration, not conversion.

Whenever possible, it is often safer to count whole units. Instead of storing dollars as 10.25, you might store cents as 1025. Then ten cents plus twenty cents is exactly thirty cents. Decimal data types can also store decimal tenths exactly, which is useful for many business applications. Of course, even decimal arithmetic cannot solve every policy question. Divide one dollar among three people and somebody still has to decide what happens to that leftover cent.

That brings us to the part computers cannot decide for you: when should rounding happen? Should each invoice line be rounded individually? Should you keep extra precision throughout the calculation and round only the final total? Depending on the situation, those approaches can produce different results. Neither is automatically right. Your business rules, legal requirements, customer expectations, and accounting policies determine the correct choice.

Floating point is not bad technology. It is an excellent engineering trade-off that makes modern computing practical. It gives us efficient calculations across an enormous range of values, which is why it is used in graphics, simulations, engineering, scientific work, and countless everyday applications. The trick is not to pretend it provides infinite precision when it does not.

Choose the right data type for the job, understand the difference between stored values and displayed values, round at the appropriate point, and test your calculations using known results. If you are comparing measured or calculated floating-point values, use a tolerance that makes sense for the problem instead of relying on exact equality.

Once you know what is going on, 0.1 + 0.2 = 0.30000000000000004 stops looking like a computer failure. It becomes a reminder that computers are very precise machines, but they are precise about the values they can actually represent, not always the tidy decimal values we had in our heads. Watch the embedded video for a fuller walkthrough and demonstrations of these ideas in action.

Live long and prosper,
RR