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.

•  Extended Cut Video - Silver

Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the database template from class plus get access to the Code Vault. If you signed up on YouTube you have to register on my YouTube Find User page so I can set up your account here on my web site. If you're not a member, Join Today!

Links

• Query Criteria: https://599cd.com/Criteria
• Parameter Query: https://599cd.com/Parameter
• CDate Function: https://599cd.com/cdate
• Update Query: https://599cd.com/UpdateQuery
• Find Between Dates: https://599cd.com/FindBetweenDates
• Search Combo: https://599cd.com/SearchCombo
• Search Form: https://599cd.com/SearchForm
• Multi-Field Search Form: https://599cd.com/MultiFieldSearch
• Search Seminar: https://599cd.com/SearchSeminar
• Access Beginner 5: https://599cd.com/XW1RK
• DateTime Seminar: https://599cd.com/DateTime

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.