Why query not finding all records with a date criteria

In query designer I have a date field.  If I enter, for example, 5/22/2015, I only get one record.  But the table has three records with that date in the date field.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
The field may also include the time.
So filtering for 5/22/2015 may require you to strip out the time component.
Jeffrey CoachmanMIS LiasonCommented:
Either that or you need to include the time.

See this simple sample
SteveL13Author Commented:
There is the date only in the table (short date format).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Format may be short, but it still may have a time int he data.  You just don't see it.

In your query, do:


and now see if it works.

Dale FyeOwner, Developing Solutions LLCCommented:

Change the "Format" property of the table to display the Long date and view the contents.  

You are probably using the Now() function (either as the default value of the field or the control in a form) to fill in that field, in which case, the actual data contains date and time values (as Jeff mentioned).  But with the format set at short date, all you see is the date component.

To do a date comparison, you could use:

WHERE DateValue([DateField]) = Forms!yourFormName.YourControlName


WHERE [DateField] >= cdate(Forms!yourFormName.YourControlName)
AND [DateField] < cdate(Forms!yourFormName.yourControlName) + 1
SteveL13Author Commented:
This is crazy.  I've tried Jim's and Dale's suggestions.  Still only get one record.  I'm going to strip this thing down and attach a condensed version.  Soon.
Jeffrey CoachmanMIS LiasonCommented:
Then post a sample database so we can analyze
For fun, instead of 5/22/2015 as a criteria, give it
Between #5/22/2015# and #5/22/2015 11:59:59 PM# and see what you get.
If it's a time/Now() issue you'll see that right off

For more fun, forget the criteria and coerce the Date to a Double in the query
Are all the decimal places the same for the three records that should be in your query?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.