Computer Learning Zone (@599CD) header
Computer Learning Zone (@599CD) profile picture

Richard Rost | Microsoft MVP | President, Computer Learning Zone. I teach Access & computer skills on YouTube to 250K+ subscribers. Trekkie, Rush fan, Lab Dad.

Fort Myers, Florida
Joined July 2009
Photo by Computer Learning Zone (@599CD)Photo by Computer Learning Zone (@599CD)Photo by Computer Learning Zone (@599CD)Photo by Computer Learning Zone (@599CD)Photo by Computer Learning Zone (@599CD)Photo by Computer Learning Zone (@599CD)Photo by Computer Learning Zone (@599CD)Photo by Computer Learning Zone (@599CD)Photo by Computer Learning Zone (@599CD)
Three words. A lost afternoon. "It doesn't work" is how a simple tech problem turns into a guessing game. Give people something to work with. youtu.be/2MkYgusTjOM
26
Need to compare a budget with actual spending? Abs gives you the size of the difference, whether you're over or under. Keep the signed difference too if direction matters. Watch my Absolute Value video to see how to use this in your queries, forms, and reports. 599cd.com/ABS
19
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. 599cd.com/Mod
20
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. 599cd.com/DateSerial
14
Your vacation has a hidden dependency: you. If the office needs you to explain one button, rerun an import, or pull a routine report, how long before the calls start? #msaccess youtu.be/ElnSpAnbMng
32
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. 599cd.com/1448Want 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. 599cd.com/1448Want 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. 599cd.com/1448
16
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. 599cd.com/InStrRev
26
Got a short list that never changes? Choose uses a number to pick an item by position. The first item is 1, so 3 selects East from North, South, East, West. Watch the video to build the expression in a query. 599cd.com/3641
21
Need the rest of a text value? Give Mid a starting position and leave off the length. In this one-space example, InStr finds the space and Mid starts one character after it. Watch the video for the query walkthrough. 599cd.com/3331
25
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. 599cd.com/Switch
23
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. 599cd.com/ACX18
1
32
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. 599cd.com/DMaxWant 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. 599cd.com/DMaxWant 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. 599cd.com/DMax
28
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. 599cd.com/YesNo
16
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. 599cd.com/NullMath
18
0.1 + 0.2 = 0.30000000000000004. Your computer isn't bad at math. The real reason is stranger, and it has caused problems far beyond an annoying spreadsheet cell. youtu.be/E3DDTKYNy0c
26