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.

????
SteveL13Asked:
Who is Participating?
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.
0
Jeffrey CoachmanMIS LiasonCommented:
Either that or you need to include the time.

See this simple sample
Database54.mdb
0
SteveL13Author Commented:
There is the date only in the table (short date format).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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:

Fix([NameofDateTimeField])

and now see if it works.

Jim.
1
Dale FyeCommented:
Steve,

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

or

WHERE [DateField] >= cdate(Forms!yourFormName.YourControlName)
AND [DateField] < cdate(Forms!yourFormName.yourControlName) + 1
0
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.
0
Jeffrey CoachmanMIS LiasonCommented:
Then post a sample database so we can analyze
0
Nick67Commented:
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
CDbl([TheDateFieldWhateverItIs])
Are all the decimal places the same for the three records that should be in your query?
0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.