Computer Learning Zone

VB, VBA, VB.NET, VBScript... Microsoft really looked at this naming scheme and said, "Good enough." They look related, but grabbing the wrong code example can waste hours. Which one are you actually dealing with? #msaccess youtu.be/U_YqdYIkeIA

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. 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. 599cd.com/DSum

That little Access password prompt may be giving you false confidence. Can you spot all 5 gotchas? #msaccess youtu.be/v6RbwCzShZE

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. 599cd.com/IIF

Two hours and 30 minutes is 2.5 hours. Calculate minutes with DateDiff("n", [TimeIn], [TimeOut]), then divide by 60 for decimal hours you can total. The "n" means minutes; "m" means months. In my Total Time Spent video, I show how to calculate and display work time, then total it by job. 599cd.com/1391

Need to change values, add records, remove records, or create a new table? Pick the action query that matches the job. This quick reference shows the four types and an important reminder: back up your database first. Watch the video for the overview. 599cd.com/1550

Round, Int, and Fix can give you different answers from the same number. Int always goes down to the lower integer, while Fix goes toward zero. Round chooses the nearest integer, with exact halfway values going to even. Watch the video to see these differences in an Access query and learn more about rounding. 599cd.com/2282

Some fictional captains entertain you. One can quietly teach you how to become a better man. Why Picard, and Patrick Stewart, meant far more to me than Star Trek ever could. #StarTrek youtu.be/N18VGZcaioY

Looking for a number somewhere in a note is not the same as checking the entire field. [0-9]+ finds a run of digits, while ^[0-9]{5}$ checks for exactly five digits from start to finish. That checks the format, not whether a ZIP code exists. Watch the video to see both tests in Access VBA. 599cd.com/5807

Your customer notes are hiding data in plain sight. Invoice numbers, phone numbers, who knows what else. Stop digging through text the hard way. #msaccess youtu.be/_QWY1CRej0A

If your fields gray out when you change a checkbox but look wrong when you open the form or move to another record, you're missing half the job. Have After Update and On Current call the same routine, so both events apply the same rule. My Gray Out Fields video shows how to put it together. 599cd.com/2333

Still writing VBA loops to find a number buried in a note? Access has a pattern-matching trick that makes that feel painfully old-school. #msaccess youtu.be/2jaCg-EBy14

A primary key identifies one record in its own table. A foreign key points back to a record in another table. In a one-to-many relationship, the same customer ID can appear on several vehicle records, while each vehicle keeps its own unique ID. Watch my video to see how the two keys work together. 599cd.com/Relationships

Think you're solid on Access basics? A customer directory, mailing labels, and one deceptively simple report layout question might humble you. #msaccess youtu.be/qUMLPWs-Xjg

Want overdue tasks to stand out? Use conditional formatting on your due-date control: red for dates before today, yellow for today, and green for future dates. Use Date() with parentheses, and remember that these rules assume dates without a time component. Watch my video to set up the rules. 599cd.com/Conditional

A validation rule checks what users enter. Validation text tells them how to fix it. This credit-limit rule accepts values from $0 through $10,000, including both endpoints. Watch my video to set up useful rules and clear messages in Access. 599cd.com/ValidationRule

Remember when 2 MB of RAM could get you started? Microsoft Access 1.0 brought tables, queries, forms, reports, and Access Basic to a Windows desktop, with a 128 MB maximum MDB size. Take a look back at the original release, 34 years after the October 28, 1992 shipping date recorded on its commemorative plaque. What was your first version of Access? 599cd.com/blog/display-artic…

"Just grab the newest record" is how you end up opening someone else's order. In a multi-user Access database, that mistake can look perfectly fine. #msaccess youtu.be/DKbp1usijqo

Need the first or last day of a month? Let DateSerial do the work. Day 1 gives you the start of the month. Day 0 of the next month takes you back to the last day of this one, even in February. Watch the video for the query calculations and the previous and next month variations. 599cd.com/2442