|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
ISO Date Addendum 1 By Richard Rost 
4 years ago
ISO Date Format - Addendum 1 - DateValue Greetings folks. Like I predicted in yesterday's video, I've run into an issue after converting my main company database to the ISO standard date format. This video will address that issue and show you how to fix it in case you run into it, too. This has to deal with using the Format function when I should have been using DateValue. Links
Keywordsaccess 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, iso date format addendum
IntroIn this video, we will talk about some important issues that can come up when converting your Microsoft Access database to use the ISO date format, especially if your database includes years of older queries and forms. I will explain common mistakes with date formatting, like using the Format function instead of DateValue, show you how to properly display just the date portion from a date/time field, and discuss how changing formats can affect your database queries and reports. This is a quick follow-up to my previous ISO Date tutorial with practical tips based on real-world experience.TranscriptWelcome to a Fast Tips Addendum video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.Yesterday, I put together a video showing you how to convert your system over to ISO Date Standard so that you could avoid confusion with international dates. As I mentioned in yesterday's video, I am making this change myself right now, and I figured I would have some updates for you. Well, I have some updates for you. I spent yesterday making this change in my database, and I spent all day today dealing with some of the ramifications that came about because my database is about 20 years old. I have been building it since 2002 when I started this company, and me from 20 years ago, let's just say I was not as good of a developer as I am today. I took a few shortcuts in some of the queries and some of the forms, which caused some problems today. Let me address these and show you how to fix them, because chances are if I made this mistake, you will probably run into it too. Before we get started, if you have not yet watched my ISO Date video, go watch it. There is the link. I will put a copy of it down below so you can click on it. The ISO dates link is there. It takes you to the Access one. I did three versions of this: one for Windows, one for Excel, one for Access. Today's video is Access specific. Go watch my video on the DateValue function, how to take a date/time value and display it just as a date. Watch this video too. Here I am in my TechHelp free template. As you can see, the dates have automatically switched over. I did not have to do anything to these. My customer sends date right there. Order dates, all this transfers right over as long as you have the short date format. I do have to say, after looking at these dates for the whole day and yesterday, I really, really like it. It is so much easier and so much more logical to look at dates that are formatted like that. Now, the number one problem that I have is because of a shortcut that I took when I did not know better. In my order table, my order date is not set to Date. I have it set to Now, and this is not bad. I recommend this if you care about what time of day the order came in. I have a web-based business, so I like to see what my busy hours are, but any business could benefit from this. If you even have a brick and mortar store and you want to know that most of your orders come in from shoppers between noon and two. When I put new orders in the system, when they come in, I will just go to the table here. New orders are coming in the system and get full date/time values. Not a big deal. The problem that I have is that 20 years ago, I did not know how to properly convert that if I just wanted to get the date value. Here is what I did: I would go to a query, like Create Query Design. I would bring in my order table. Here is the order ID and here is the order date. If I wanted to aggregate this or whatever so I could just get a single date value for that instead of the time portion, what I did 20 years ago is I used the Format function. I would say, like, DateOnly: Format([OrderDate], "mm/dd/yy"), which was the standard date format that I was used to. No problem. Take this, run this through an aggregate query, and it worked just fine for the past 20 years. The problem is, now I had to go back through my entire database. I spent a couple hours today changing queries that have that hard-coded format in them, changing a lot of my VB code where the Format function is in there, because it is obviously not going to be compatible now with the new date format. In addition, when you use the Format function like that, it changes the value to text. This is actually a text value. What gives it away is if you look here, you see how that date lines up on the left side of the - I want to say the cell, but the column, the field. Whereas a proper date and a number field lines up on the right side like they should. That should be your first indication that it is actually no longer a date value. So what I would have to do is, if I wanted to use this in something that involved a date, I would have to convert it back to a date. I would take that and use CDate with the converted date format. Then I run that, and now it is back to a date value. But the problem is that this Format function can cause issues. The proper way to handle that is to use the DateValue function. I went through today and I replaced all those Format commands with DateValue. DateValue([OrderDate]) just gives you the date part only and it leaves it as an actual date field, and it will display using your short date format. This was most of my day today. That took about two or three hours just going through and cleaning up a lot of that, because I have not built my database for a long, long time now. I did not know about this back then, so you are benefiting from my mistake. Part of the way that you learn this kind of stuff is to make mistakes and screw up; that is when you learn. That is how you remember stuff. Another thing you can do if you only want to see the date displayed differently is change the Format property. For example, right now I have got that. If you only want to display this value, you can change the Format property right here in the query or in a form field. Just go to the Format property and use yyyy-mm-dd. If you want the time on there, right hh:nn for example. That will change the way that that field is displayed, but the data is still in there. Even if you come in here and say you want to see this as a short date, the data is still in there with the full time. If you want to do something where you are matching an update by day or you are making an aggregate query out of it, that is not going to do it. That will change the way it looks, and a lot of the time I do that on my forms right now, put that format in the field so it will display that, because that is generally what I want to see - only the date. But if I click on it, I do want to be able to edit it down to the second. So there you go. Just a quick addendum for today. I wanted to share that with you. I will probably have more as I go along, but everything seems to be running just fine. Of course, if you have any questions or comments, feel free to post them down below. We will see you soon. How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you have finished the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website. But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free. QuizQ1. Why did the instructor recommend switching to the ISO Date Standard?A. To avoid confusion with international dates B. To make dates look fancier C. To compress data in the database D. To automatically detect time zones Q2. What main issue did the instructor encounter after switching over to ISO Date format? A. Losing all time data in orders B. Old queries and forms used the Format function with hardcoded date formats C. Customer data was deleted accidentally D. Payments stopped processing in the database Q3. What does the Format function do to a date value in Access? A. Changes it to a text value B. Converts it to a number C. Encrypts the value D. Duplicates the field Q4. How can you tell in Access if a field is being displayed as text instead of a true date or number? A. It aligns to the left side of the field B. It turns red in color C. The field becomes read-only D. It shows an error icon Q5. What is the recommended function to extract just the date part from a date/time value and keep it as a date in Access? A. DateValue B. Format C. CDate D. StringValue Q6. If you want to visually change how a date is displayed in a query or form, but not alter the data, what should you adjust? A. The Format property B. The data type C. The primary key D. The input mask Q7. Why did the instructor originally use the Format function in his queries 20 years ago? A. He did not know a better way to extract just the date value B. He needed to sort data alphabetically C. It was required by Access at the time D. The DateValue function did not exist Q8. What is a drawback to using the Format function for displaying dates in aggregate queries? A. The field becomes text, which may cause issues when using it in other date functions B. It encrypts the dates C. It removes all date formatting D. It turns the field into a number Q9. What is true about the Format property in a query or form? A. It only changes how the data is displayed, not the underlying data B. It changes the stored data in the table C. It resets any relationships in the database D. It prevents editing the value Q10. For matching or aggregating dates by day regardless of time, what is the best practice shown in the video? A. Use DateValue to strip the time B. Use Format to convert to text C. Use CDate on all fields D. Change the data type to string Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers some important follow-up information about switching your Access database over to the ISO Date Standard to avoid confusion with international date formats. As I mentioned in my previous video, I recently made this change myself, and after working through the update, I ran into some issues that I want to share with you so you can avoid similar problems.My database has been a work in progress for about 20 years, and when I first started building it, I sometimes took shortcuts—especially in my queries and forms. If you have also been developing in Access for a while, you might encounter similar issues when making changes like this. The first thing to note is that if you have not yet watched my original video about converting to ISO Date formats, I highly recommend you start there. I also have videos covering this process in Excel and Windows, but today I am focusing specifically on Access. Watching my tutorial on the DateValue function would also be helpful, as it teaches you how to display just the date portion from a date time value. Looking at my TechHelp free template database after the change, you will see that the dates have switched over to the ISO format automatically, as long as you use the short date format. I have to say, after using this format for a full day, I find it much more logical and easier to read. The main problem I ran into comes from an old shortcut I used in my order table. My OrderDate field stores a full date and time value by default using the Now function. This is actually a good practice if you want to see what time orders arrive, which is useful for both web-based and brick-and-mortar businesses. When new orders are entered, they include both date and time, which gives a complete picture of ordering patterns. However, years ago when I wanted to pull just the date part from that field, I used the Format function in my queries. I would create a calculated field using Format, such as "Format([OrderDate], 'mm dd yy')," to strip off the time. This approach worked fine for years, but it causes trouble now because the Format function converts the date to text instead of keeping it as an actual date value. One way you can spot this is by noticing that text values align differently than true date or number fields in your datasheet—text values align to the left, while dates and numbers align to the right. If you later need to treat this formatted field as a date—for example, to compare or group records by date—you would have to convert it back using something like the CDate function, which can get messy and is not a recommended solution. The better approach is to use the DateValue function, which extracts just the date portion from a date time value and returns it as a true date. This way, your queries and forms continue to function correctly, especially with the new ISO date format, and you avoid the issues caused by storing dates as text. I spent several hours today finding and fixing all the old Format commands in my database, replacing them with DateValue instead. It had been a long time since I did this kind of cleanup, but catching these issues gives you a chance to learn and remember better practices. If you only want to change the way a date field looks, rather than altering the underlying data, you can adjust the Format property for that field—whether in a query or on a form. Set the format string to something like yyyy-mm-dd for dates, and add hh:nn if you want to display the time as well. Remember, this only affects how the data is shown. The full underlying data, including the time, remains unchanged. However, if you are running an update or aggregate query and want results grouped by day, altering just the display format will not be enough—you need to use DateValue to actually work with the date part of your data. That sums up my main findings for today as I continue to upgrade my systems. If you have any questions, please leave a comment. For those interested in becoming a member, you can join and select from various membership levels, each with its own perks. Silver members gain access to extended cut TechHelp videos, free beginner classes each month, and more. Gold members can download the sample databases I use and access my code vault. You will also receive higher priority if you send in TechHelp questions and unlock free expert courses after finishing the beginner series. Platinum members receive everything from the previous levels plus even more priority and additional full-length courses, not just for Access but also for Word, Excel, Visual Basic, and others. No matter your membership status, I will continue to make these free TechHelp videos available for everyone. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListProblems caused by using Format function for dates in AccessFormat function converting date values to text Issues with date alignment in query outputs Using CDate to convert text dates back to date values Replacing Format with DateValue for extracting date portion Using DateValue function to keep date fields as dates Changing query or form field Format property for display Displaying dates as yyyy-mm-dd and adding time formatting Limitations of using Format property for aggregate queries Retaining time information while displaying only the date |
||||
|
| |||
| Keywords: FastTips Access iso date format addendum PermaLink ISO Date Format in Microsoft Access Addendum 1 |
