Sorting by date


i'm having trouble filtering data by "Date" on a subform. The situation is: I have two comboboxes and with them you specifiy the range of dates that should be displayed on the subform. Let's say that their names are: "Date1" and "Date2". I used two methods to filter, but both were not successful. First i used:

[MainForm_Subform].Form.Filter = "MYDate>=#" & Date1 & "# and MYDate<=#" & Date2 & "#"

(MYDate is the filed which contains dates)

It seemed OK, but then i noticed that when i selected let's say 1/1/00 in the first combo and 4/12/00 in the second, the records displayed were actually from 1/1/00 to 12/4/00, so the days were considered as months and vice versa.

Next i used the BuildCriteria method:

[MainForm_Subform].Form.Filter = BuildCriteria("MYDate", dbDate, ">=" & Date1 & "and <=" & Date2)

, but then something strange happened. I got the error message "You cancelled the previous operation". I was put in the debug mode and i checked what was the produced criteria like. It was:  

MYDate>=#1/1/2001#/0 And MYDate<=#12/4/2000#

I really don't know where the expression  #1/1/2001#/0  comes from, and also if that expression was correct, in the second date the months are used again as days??

What am i doing wrong??????
Who is Participating?

Improve company productivity with a Business Account.Sign Up

nico5038Connect With a Mentor Commented:
I tested this query:
SELECT Name, DateTest FROM tblNames WHERE (((DateTest)>#1/2/2000#));

With regional setting on ".", "-" and "/" and had no problem.
Entering e.g.:
SELECT Name, DateTest FROM tblNames WHERE (((DateTest)>#1-2-2000#));
With settings like "/" were working.
The query criteria were only displayed in the "regional setting delimiter" afterwards.

Just give it a try!

You're "tricked" by the combination of regional settings and the US-dateformat (mm/dd/yy) access uses.
To be 100% sure, I always use yyyy/mm/dd format when using dates between #'s, as there is no switch of day and month possible!

BTW the best filter will be using BETWEEN iso ">= AND <=", as access will perform for the BETWEEN only one compare iso two for the ">= AND <=" !

nico5038, why yyyy/mm/dd ?
I can't use my locale format, since DB can't handle it, so I convert everything to US format (mm/dd/yyyy). I didn't found any document that would recommend some format...
What are your reasons for yyyy/mm/dd ?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

mario_34Author Commented:
Hi nico5038,

how do you obtain that the dates are stored exactly in the
yyyy/mm/dd format ?
I must add that I've always used the US date format, mm/dd/yyyy which, whilst it makes no sense to me as a Brit where the logical date order is dd/mm/yyyy, this always seems to work with Access. Therefore:

WHERE MyDate >= #(your date as format mm/dd/yyyy)#
AND MyDate <= #(your date as format mm/dd/yyyy)#

Or as Nico suggests, use the BETWEEN operator.

Internally Access will store a date as a relative daynumber starting on 31-12-1899.
The way a date is seen depends on the regional settings and input-mask.
You can constructed a field looking like:
and after re-typing exactly the same date you'll get:
The yyyy-mm-dd format is safe as Access always assumes after a fourdigit year a month will follow.
Otherwise it sometimes checks the fields for being >12 and concludes then it's a day, when the value is less however it takes it as a month...

Thus I often even "drop" the date format totally and just use a number like 20010809. That sorts OK.

I have alwaysd used date in the format #dd-mmm-yyyy#

This comes out as #12-MAR-2001# - it is recognised as a valid date by Oracle, Access, SQL Server etc.  
mario_34Author Commented:

i used the following code:

[MainForm_Subform].Form.Filter = BuildCriteria("MYDate", dbDate, ">=" & Format(Date1, "dd/mm/yy") & "and <=" & Format(Date2, "dd/mm/yy"))

 and it seemed to work correctly since i brought the database to another computer. There i got an error message saying "The expression you entered has invalid . (dot) or ! operator or invalid parentheses". On the previous computer i had the date separator set to "/", but on the current it's set to ".", so i changed it to "/" and it was all OK. So, how can i make the filter indepenedent from  that settings ?

I normally use a dash ("-") and have little problems.

BTW I would set the filter with a date-type field like:
[MainForm_Subform].Form.Filter = "MYDate BETWEEN " &  Date1 & 
" AND " & Date2
Access will do the translation then for you.
When you have stringfields use:
[MainForm_Subform].Form.Filter = "MYDate BETWEEN #" &  Date1 & 
"# AND #" & Date2 & "#"

But then the format must be OK....

mario_34Author Commented:
Hi nico,

you said that you use the dash, but as i said before, the filter should be independent from this setting, because otherwise i (or the user) should change the regional settings every time the database is brought to another computer. So, is it even possible to make it independent?

mario_34Author Commented:
Hi nico!
I finally solved the problem!
I used the following syntax:

[MainForm_Subform].Form.Filter = BuildCriteria("MYDate", dbDate, ">=" & [Date1].Value & " AND <=" & [Date2].Value)

Sorry, i know you suggested me to use BETWEEN, but once it worked, i let it as it is now. Btw, the problem was in the use of brackets "[ ]". In the beggining i used this syntax:

..." & Date1 & "...

and, as i said, i always got an error message if the date separator was set to "."
Then i changed it to

..." & [Date1].Value &  "...

As you see above i also added the .Value property, even if i don't know if it's really necessary, but it works, so...

Could you please tell me what's the difference between Date1 and [Date1] .

It should make no difference to use Date1 or [Date1], however sometimes Access will translate the text into a string....
Just check this in the criteria part of the query editor.
To force Access to see it as a field (iso textstring) the brackets ("[]") can be used. In case you have a fieldname with an intermediate space, you need to use them anyhow.

Indeed the ".value" is obsolete, as it's the default property of a field, but it's always clear when you specify it.

Just copy/paste your query and try:
[MainForm_Subform].Form.Filter = "[MYDate] BETWEEN " &  [Date1] & 
" AND " & [Date2]

This saves the "BuildCriteria" (new to me!) and it's exactly what you would specify behind the WHERE of a query.
I personally use filters and pass them as the "WHERE-part" parameter when activating a report or other form....

Glad it's working now and success with the application.

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.

All Courses

From novice to tech pro — start learning today.