Between Wrong
By Richard Rost
6 years ago
Be Careful Searching Dates. Between May Return Unexpected Results
In this Microsoft Access tutorial, I will teach you how search for records such as orders between two dates. Then, I'll show you what problem occurs if you also have TIMES in your order date field and your queries aren't returning all of the records.
Jorge from Grand Forks, ND (a Silver Member) asks: I have an OrderDate field in my order table. I'm using the Now() function to set the default order date so that I don't have to type it in every time I create a new order. The problem is, whenever I try to run a query showing all of my orders between two dates, the orders on the LAST date don't show up! This happens whether I use the BETWEEN keyword or inequalities. What am I doing wrong.
Members
I'll show you how to create a second calculated field in your query that represents ONLY the date portion. You can then use this for your calculations. Then I'll show you how to use an update query to remove that time portion from your order date field permanently, if you choose.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Subscribe to Between Wrong
Get notifications when this page is updated
Intro
In this video, we will talk about handling date ranges in Microsoft Access queries, specifically the issue that occurs when using the BETWEEN keyword with order dates that include time values from the NOW function. I will explain why some records do not appear when filtering between two dates, show how to correctly structure your query using inequalities and the CDate function, and discuss best practices for storing dates versus date and time values in your tables.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost. In today's video, we are going to look at searching by dates. Specifically, when we have a query where we are using the BETWEEN keyword to look for orders between two dates, and it is giving us the wrong results.
Today's question comes from Jorge from Grand Forks, North Dakota, a silver member. Jorge says, I have an order date field in my order table. I am using the NOW function to set the default order date so that I do not have to type it in every time I create a new order. The problem is whenever I try to run a query showing all of my orders between two dates, the orders on the last date do not show up. This happens whether I use the BETWEEN keyword or inequalities. What am I doing wrong?
Well, Jorge, there are a couple of things it could be, and since you emailed me privately and sent me a sample of your data, I know exactly what it is. But let me explain what is happening for you and all the folks at home. This is a common problem and I see it a lot.
Here is my simple database that I use in my free customer template. You can download this from my website. I have got real simple customers, orders, contacts in here. Let's go to my order table, design view. Now I do not have order date in here because, again, this is just a simple database for training. But I am going to add order date. That will be a date/time field. I will put the default value down here equal to NOW. That puts the date and time in that field when new records are created. Let's save that. NOW is accurate to the second.
Let's go into my order table now and put some dates in here. Let's say we have got 10/1/20 for an order. We'll do 10/15/20. Let's do 10/31/20, and so on, maybe 11/1. We'll do 11/15. Let's put a couple of earlier ones in here: 9/1, 9/10, and so on. We are going to search for all the orders from October. There should be three of them when I do my query.
Let's make the query next. Save changes. Let's go into Create, Query Design, bring in my order table. If you do not know how to do this, I have got lots of lessons on making queries using query parameters, which we are going to do in a second here, and other videos on searching. I will put links in the description below the video for you to follow and watch those for free.
Now, let's bring down into the query OrderID and OrderDate. That's all I care about right now. I just want to see the ID and the date the order was placed. If I run the query right now, you will see there are all my results. Now, I want to limit this to see only the orders from October. So let's go back to design view.
I am going to come down here for criteria. There are a couple of ways you can do this. You can hard code the dates in there. You can say BETWEEN and then inside of hashtags or pound signs or octothorpes, whatever you want to call them, I can say 10/1/2020 and 10/31/2020. Just like that. That is if you want to hard code the dates.
Let's save the query. I will call this my OrderQ. When I run it, there are my three records. Ignore the new record on the bottom; that is just a new blank record coming in there. But there you can see, there are all the orders, the three orders from October. That is one way you can do it.
The second way you can do it is you can come in here and say BETWEEN and have the user type in the dates every time you run the query. This is called a parameter query. I can say BETWEEN [start date] AND [end date]. Whatever you type in between those square brackets becomes the prompts. Now if I save the query and run it, I can type in the start date, 10/1. If you do not put a year in there, Access assumes the current system year. 10/1 and 10/31. There you go. There are the three of those again.
What Jorge meant by inequalities is you can also say something like this. You can say greater than or equal to [start date] and less than or equal to [end date]. It is the same thing. If I run it again, 10/1 and 10/31, there we go. There are all three of them.
Why is Jorge having a problem? The problem is not how Jorge is setting up the criteria. The problem is with the data in the table. Let's close the query for just a minute. Let's take a look at the table, OrderT. Look at this blank brand new record that is coming in. We used the NOW function to put a default value in there. Right now it is 10/28 at 5:11:41 p.m. That is the exact system time that I have. So let's say an order comes in right now. Customer ID 4, the order amount is $400. It is now 10/28. See the next record will get 5:12. Let's say I want to see a list of all the orders from 10/1 to 10/28, today's date. All right, 10/28.
If I run my query, 10/1 to 10/28, I am not seeing it. There is the blank new record, but I am not seeing my record for $400. Why is that? If you look at the data, 10/28 and 5 hours later, that is greater than 10/28. Remember, days are the same as saying this day at midnight. So if you are using NOW, you are putting a time in there. That time is technically after 10/28 at midnight.
If I were to delete these records here, I put 10/21 and 6 p.m. in there and then run that query: 10/1 to 10/31. I do not see it because 10/31 is before 10/31 at 6 p.m. Remember, midnight is the default.
There are a couple of ways you can fix this. The first thing that I would recommend is to not store times in your orders. Instead of using the NOW function, use the DATE function instead. In order date, use =DATE instead. That is the best thing you can do for orders. Unless, of course, you need to track orders to the second. I have had clients that had to do that. They want to know exactly what time of the day an order came in. If that is what you are going to do, you have to make sure you add one to your end date. Either train your users to type in one day ahead or you can add one to your end date.
Now you cannot just add one here like this. I will show you why. When I run this now, 10/1, 10/31, you get this: Expression is typed incorrectly. Why? That is because end date comes in as text and you cannot add one to text. You have to convert that text value over to a valid date. We can do that with the CDate function. Say CDate([end date]) + 1. Run that. 10/1 to 10/31. Now I am good.
The problem with that is now I am seeing 11/1 as well because 10/31 plus one is 11/1. In this scenario, you have to make sure not to use the equal sign. It has to be greater than or equal to [start date] and less than CDate([end date]) + 1. This will make sure all the records that you see are greater than or equal to 10/1 and less than but not equal to 11/1.
Again, if I run the query now, 10/1 to 10/31, there you go. I do not see 11/1. This also means now that you have to use the inequality symbols. You cannot say BETWEEN [start date] AND CDate([end date]) + 1 because if you do that, it will include 11/1 because BETWEEN includes the end points. So you have to use the inequalities. You cannot use BETWEEN anymore.
Another way you could do that is to create a second field that has just the date portion in it and then you can continue to use the BETWEEN keyword. Or, if you decide you do not need those times, you can go through and just delete them out of your table. Both of those techniques I will show in the extended cut for members.
In the extended cut for members, we will build a date-only query field so you can keep those times and then make a second date in your queries that has only the date portion. That is handy because then it makes all of your date calculations much easier and you do not have to worry about that time problem.
Then, secondly, if you decide you do not want those times, if you only want the dates stored in your table, I will show you how to fix it. I will change that NOW back to a DATE and I will show you how to create an update query to remove all of the time portions from those date values.
How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. 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.
These 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.
Quiz
Q1. What is the primary reason Jorge is not seeing orders on the last date when searching with the BETWEEN keyword?
A. He is missing data in his order table.
B. The query syntax is incorrect.
C. Orders are stored with a time component because of the NOW function.
D. The database is not saving any records after a certain date.
Q2. What does the NOW function return when used as a default value in a Date/Time field?
A. Only the current date at midnight
B. Only the current time
C. The current date and time down to the second
D. A static date chosen by the user
Q3. When searching for orders between two dates, why might orders enter on the end date not show up when using the BETWEEN operator?
A. Access automatically skips the last date.
B. The order records are corrupted.
C. Time values make the record later than midnight of the end date.
D. The query design view is hiding records by default.
Q4. When a user types in [start date] and [end date] for a parameter query and only enters month and day (e.g., 10 1), what does Access assume for the year?
A. It prompts for the year
B. It uses the year 2000
C. It uses the current system year
D. It results in an error
Q5. Which function should you use as the default value in your orders table if you only want to store the date and not the time?
A. TIME
B. TODAY
C. DATE
D. NOW
Q6. If you need to include orders for the entire last day in a date range that includes time values, which approach is recommended?
A. Use BETWEEN with the original end date
B. Add one day to the end date and use less than (<) instead of less than or equal to (<=)
C. Use only the greater than (>) symbol on the start date
D. Store time as text
Q7. Why does Access return an error when you try to add 1 to [end date] in a query parameter directly?
A. The [end date] parameter is always null
B. [end date] is interpreted as text and cannot be incremented
C. You are only allowed to use mathematical operations on numbers
D. You can only add time, not dates, in queries
Q8. What function is used in Access to convert a text value to a date value so that you can add one day to the end date parameter?
A. DateAdd
B. Now
C. CDate
D. ToDate
Q9. Why do you have to use inequality symbols instead of BETWEEN when you use CDate([end date]) + 1 in your date criteria?
A. BETWEEN only works with text values
B. BETWEEN would include dates beyond the intended range
C. BETWEEN does not accept parameters
D. BETWEEN is not supported in queries
Q10. What is one alternative suggested in the video to fix the issue without changing how times are stored in your orders table?
A. Delete all records with time components
B. Sort records by time instead of date
C. Create a query field that strips the time portion and then use BETWEEN on that
D. Always filter using only the date portion manually
Answers: 1-C; 2-C; 3-C; 4-C; 5-C; 6-B; 7-B; 8-C; 9-B; 10-C
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.
Summary
Today's video from Access Learning Zone focuses on a common issue with searching by dates in Microsoft Access queries, specifically when using the BETWEEN keyword to look for orders placed between two dates but the search is omitting records from the last date in the range.
The situation came up from a question regarding an order date field that uses the NOW function as its default value. The intent behind using NOW is to automatically set the order date and time for each new record, making data entry easier. However, the problem arises when running a query to find orders between two dates – the orders placed on the last date do not appear in the results. This issue occurs whether the BETWEEN keyword or inequality symbols are used for the criteria.
The root of the problem comes from the difference between date values and date-time values in Access. When you use the NOW function, you're capturing not just the date but the precise time as well. In database terms, a date such as 10/28/2020 is actually stored internally as 10/28/2020 at 12:00 AM (midnight), unless a specific time is provided. So, when you use BETWEEN 10/1/2020 AND 10/28/2020 to find all orders in that span, Access looks for entries from the start of 10/1/2020 through the very start of 10/28/2020 at midnight. Orders with a timestamp later on 10/28 will not match the criteria.
There are several ways you can resolve this issue:
First, if it's not necessary to record the time for your orders, use the DATE function instead of NOW when setting the default value for your order date field. DATE only records the date without the time, so your BETWEEN queries will work as expected since all records will have midnight timestamps.
If you do need to capture the time, consider shifting your query's end date criteria. Instead of "less than or equal to" the ending date, change your search to include only dates that are "less than" the next day. For example, if your query normally looks for orders between 10/1/2020 and 10/31/2020, adjust the logic so it selects orders from 10/1/2020 up to but not including 11/1/2020. This ensures all times on the final date are included. To do this effectively, you need to convert user input (which comes in as text) into a date value using the CDate function, then add one to the end date, like CDate([end date]) + 1. When doing this, make sure to use the proper inequality symbols: greater than or equal to your start date and less than the adjusted end date. Do not use BETWEEN with this approach, since BETWEEN includes both endpoints and will bring in the following day's records as well.
You also have the option of creating a calculated field in your query that strips out the time portion, leaving you with just the date. This allows you to continue using the BETWEEN keyword reliably. Alternatively, if you decide tracking the time is unnecessary, you can update your existing order records to remove the time component. Both of these strategies can make date searching more consistent and less prone to errors.
In the Extended Cut for members, I demonstrate how to build a calculated query field that extracts just the date from your order date-time values. This allows you to retain your detailed time data, but base searches and calculations only on the date. Additionally, I cover how to update your existing order table to remove all time information, and switch your default value from NOW to DATE, so only dates are stored from now on.
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 List
Adding a date/time field with the NOW function as default
Demonstrating issues with storing time in order date fields
Creating queries with hard-coded date criteria using BETWEEN
Building a parameter query to prompt for date ranges
Using inequalities (>= and <=) instead of BETWEEN in queries
Explaining why times affect date range query results
Difference between NOW and DATE functions in Access
Fixing date range queries by converting end date to date type
Using CDate function to manipulate date criteria
Correct technique for using less than CDate([end date]) + 1
When and why BETWEEN does not work with date/time fields
Recommendations for storing only dates in date fields
|
