X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Query Date Criteria, Access 2010    
post Dec 20 2017, 05:19 PM

Posts: 26
Joined: 23-March 09
From: Auckland, NZ

I have a unusual problem with a date criteria. When i put Date() as criteria on a date column is returns results when the data is 21/12/2017 , But if the data is 04/12/2017 it does not.
It seems like if the date was 4/12/2017 it works but not if it has a zero in the date it does not.

Is there a way around this ?
Go to the top of the page
post Dec 20 2017, 05:37 PM

UtterAccess Editor
Posts: 18,033
Joined: 29-March 05
From: Wisconsin


First, it appears that you're using European format (day/month/year) rather than American format. If you could fill in your location in your profile information, that might cut down on any confusion in the future.

Second, I'm seeing something in your question that may be important. You seem to be saying that "04/12/2017" doesn't show up in the query but "4/12/2017" does. This seems to indicate that your data is a TEXT datatype, not a DATE datatype.

Are you perhaps using a Format() function? That would return a string value.

It also might help to see the query's SQL.

Hope this helps,


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
post Dec 20 2017, 06:53 PM

Posts: 26
Joined: 23-March 09
From: Auckland, NZ

Yes thanks for that , I am in Auckland NZ.
I see the some of the Fields are set as text not date like you said. I'll have a look at changing this and see what happens.
Thanks for your help
Go to the top of the page
John Vinson
post Dec 21 2017, 01:39 AM

UtterAccess VIP
Posts: 4,163
Joined: 6-January 07
From: Parma, Idaho, US

Note also that - since Microsoft's programmers were American - Access is programmed to accept literal dates in criteria, but it interprets them in the (admittedly irrational) American mm/dd/yyyy format. Therefore a criterion of 4/12/2017 is interpreted as April 12, 2017, while you may INTEND it to mean 4 December 2017. This does not affect the Date() function, because that function returns a Date/Time datatype, a Double Float number count of days since 30 December 1899; this number value is also used for all table and query Date/Time fields. Formats are only applied when a human needs to look at the value.

John W. Vinson
Wysard of Information
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd January 2018 - 07:06 PM