Got date problems. Kind of like Got Milk. If you got date problems after a recent switch, maybe to the ISO date standards, well, I'm going to show you what one of the most common problems I see with people working with dates, especially beginners. This one's for you beginners. And I'll be honest, I made this exact same mistake for years when I first started using Access databases. For years, I did this. I'm still finding spots in my database from years ago where I did this. And I'm going to show you how to fix it.

Today's question comes from Landon in Sandy Springs, Georgia, one of my platinum members. Let's see what Landon has to say. We're going to try this voice synthesis one more time. We'll see if we can get Sammy to warm up to it.

Hi. I recently switched my database to use ISO date formatting as you instructed in one of your videos, and now some of my queries and VBA code aren't functioning as they used to. I am uncertain about what's causing the issue, but it seems that my methods of handling dates are no longer yielding the correct results. For example, query criteria no longer work. Could you help me identify the problem and find a solution?

I don't know. What do you think? I'm still on the fence myself as far as using this voice synthesis thing goes. I might as well give them a plug while I'm at it. It's a site called SpeechMade. It's basically copy and paste in here. You can pick any kind of voice you want, what language you want, what country you want, and then it gives you samples down here. It's pretty cool. I like it. I don't know if I'm going to keep using it or not. What do you guys think?

Anyways, Landon's problem is a problem that I had myself like I said, and I've seen it coming from a lot of people. The problem is the way he's handling the dates in his system. Now one of my missions in life is to get everyone in the world using the ISO date format. It's year, month, day, just like this. It's universal. Everybody can understand what it means. Computers work great with this because this is the best way to sort dates. Year first, then month and day. Month, day, year, silly. If anything, I like the European style day, month, year. That makes more sense. But this makes the most sense. So one of my missions is to get everyone to switch this. I switched this myself a couple of years ago. It's 2025. I think I switched back in 2022.

The problem is, if you have an Access database or even Excel spreadsheets and that stuff, where you've been relying on a specific format to work in your database, then you might have to retool those formats and change things around the way you're working with the database.

Here I am in my TechHelp free template. This is a free database you can grab on my website if you want to. In here, I've got a customer table and in my customer table, I've got a field called customer since. As you can see, I've switched my system over to ISO dates. It's something that you change at the Windows level and then anywhere you have a short date, which is the default date setting in Access or even in other programs, it should appear like this unless that program specifically overrides it.

Now let's make a query and I am going to, for the purposes of class, reformat that date and make it look like a USA formatted date. I'm just going to bring in the customer ID. I'm going to bring in customer since and then I'm going to make a new field over here. I'm going to call it USA date: I'm going to use format, which a lot of people do. I'm going to format that customer since field as MM/DD/yyyy. I will zoom in so you can get a better look at that. That's what I'm formatting the customer since as using format. People do this all the time. There's nothing wrong with this as long as it's the final step in your procedure. Only use the format function to display that date the way you want to display it. Don't use it in your queries. Don't use it in your calculations. If the boss wants to see this format or any other specific format, that's fine. Display it that way on whatever form he works with or on whatever report you have to generate. But don't rely on this format for stuff inside your database.

I'm going to save this as, let's just say this is Q1, whatever. Doesn't matter. The first thing you can see here is there's my date in that format. Notice how the date here lines up on the left side of the column. Over here, the date is lining up on the right. Remember, numeric values, which include auto numbers, date values are numeric values. Numbers, currency values, those all line up on the right side unless you specifically change their format. Text values line up on the left. This should be the first thing right here telling you that that's not 100% right. Something's up with this. It's formatted as a date, but it's actually a text string.

Access can still kind of work with this. There's still stuff you can do, but here's the thing. Here's the next step that people take. Let's say you need to get the year off of that. And again, I'm guilty of this myself. I'll call it Y, and I'm going to get the right characters from USA date. It's a field I just made, comma, four. I'll zoom in so you can see that better. Y is the right characters from USA date, comma, four. Give me the four right-most characters from that. Run it and there you go. That looks normal. That looks perfectly fine, but look, it's on the left-hand side. It's a text string. It's not an actual year. It's not a number.

Access is actually pretty good at dealing with this. If you do treat it like a number, like if I come in here, if I say Z is going to be Y plus one, that should actually work. And it does work, and it actually converts it to a number because Access can do something called on-the-fly type conversion. It looks at this and says, well, you're trying to take a string value and a number to it. Is this string actually a number? Yeah, okay, we can do it. We'll let you do it this time, but it doesn't always work. So don't rely on it. The problem is, people now have this year thing here, and they think that that's a number they can work with as a number, and it's not. It's text, and it won't always work like a number.

This becomes evident if you've got this formatted year, and let's say we try to put some criteria on it, like greater than or equal to begin date. This is called a parameter query. I'm going to give you some links to some videos to explain some of this stuff. This normally says, the user can type in the begin date, and it will then give me all the dates after that. If I run that, it's going to ask for begin date. Let's go 1/1/2000 and no, it's not working. It's giving me 1955's, 1987's, but it's also missing a bunch of dates. It's missing everything from January through October. What's going on? The problem is it's treating it like text.

Usually, this happens like in an earlier query. Let's say you've got Q1. This is query 1. Let's get rid of that. Save this. Now I'm making another query based on that query. I'll bring in Q1, and then I bring in my formatted date. Then I run it. There's all my dates. Looks good. They look like dates. Now in here, I put in greater than or equal to begin date. I run it. This is the exact problem that they had, by the way. I had Landon send screenshots, and yep, this is exactly what's going on. They type in 1/1/2000, and it does a text-based comparison to those two things. That's not the way that you do it.

Like I said, I'm guilty of this myself. In my first couple of databases, I used to always use, give me the right four characters to represent the year, and that is not good. You don't do that. So what do you do? Like I said earlier, only format the date in a different way like this where you're displaying it. But that's it. Don't rely on this for any other kind of numerical data. If you need to get the year, don't use the right function, use the year function. Y is the year of customer since. There's a year function. There's a month function. There's a day function. That's an actual year. And look at that. It's lined up on the right side of the field.

If you want to put your criteria here, put it on an actual date field, greater than or equal to begin date like that. Do you have to always type in your date in the right format? No. 1/1/2000. It should still work. Access is pretty good about knowing what type of data you're typing in. It got all the right dates. You can still type it in the normal ISO format. That should get that. There you go.

That is regional, I believe, though. If you're in Europe and your regional settings are set to European, UK, whatever, it's going to go day, month, year. It matters because if you type in 1/2/2000, is that January 2nd, or is that February 1st? See, that's why this format is dumb. The best format is 2000-01-02. Now, you know exactly that that's the year 2000, the month is January, and the day is the second, and you run it, and you get the right balance. That's why I'm a big proponent of the ISO date format.

That is why you only use the format function at the last step. Either in your reports or in the form of the user, you have to see it, and they want it a specific way everywhere else, you use these functions and you don't use format, you don't use left and right. Don't treat it like a string.

Here are some other videos for you to watch. Here's the format property and function. Here are those string functions, left, right, mid. Learn these. These are definitely good, but don't use them for dates. Here's my video on query criteria. That's this thing down here, that's a criteria. Specifically, having it where the user can type in their own criteria, that's called a parameter query. Learn more about this. I cover all this stuff in my beginner classes, by the way, this is all beginner stuff.

If you want to take a piece of that date, there are separate functions for year, month, and day use those. You can separate a date into any of its components. There's a ton of different date functions. You can see I cover lots of them in Beginner 5, Expert 27, Expert 28. I spend lots and lots of time on date stuff. In fact, I have an entire seminar called the Date Time Seminar, where I cover pretty much everything there is to know about working with dates. I calculate workdays between two, if it's like January 1st and January 7th, how many workdays are between there, all kinds of stuff. Holiday is a recurring appointment. You name it. If it has to do with dates, it's in this seminar. I'll put a link to it down below, check it out.

But there you go. That's your TechHelp video for today. I hope you learned something. I wish I would have had this video back in like 1994 when I was starting working with Access. I'll have saved myself some time and some headache. But that's going to do it, folks. Live long and prosper my friends. I'll see you next time.

TOPICS:
Common date handling problems in Access
Switching to ISO date format
ISO date standard (yyyy-mm-dd)
Impact of date formats on Access queries
Format function for date display
Issues with using format in queries
Access date fields vs text fields
On-the-fly type conversion in Access
Using Year, Month, Day functions in Access
Parameter queries in Access
Defining query criteria in Access
Importance of proper date formats in Access
Consequences of incorrect date handling

COMMERCIAL:
In today's video, we're discussing a common issue beginners face when switching to the ISO date format in Access databases. If you've been struggling with queries and VBA code not functioning correctly, this video is for you. You'll learn why you should only use the format function for display purposes, and instead use specific functions like year, month, and day for precise date calculations. We'll correct these mistakes and make sure dates are treated properly as numbers, not strings, which is crucial for accurate querying. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.