Solved

Sorting by date

Posted on 2001-08-10
12
185 Views
Last Modified: 2006-11-17
Hi,

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??????
0
Comment
Question by:mario_34
12 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6371959
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 <=" !

Nic;o)
0
 
LVL 2

Expert Comment

by:BozzoCage
ID: 6372023
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 ?
0
 

Author Comment

by:mario_34
ID: 6372048
Hi nico5038,

how do you obtain that the dates are stored exactly in the
yyyy/mm/dd format ?
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6372208
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.

J.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6372313
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:
 03/02/01
and after re-typing exactly the same date you'll get:
 02/03/01
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.

Nic;o)
0
 
LVL 15

Expert Comment

by:cquinn
ID: 6372586
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.  
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:mario_34
ID: 6372893
Hi,

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 ?



0
 
LVL 54

Expert Comment

by:nico5038
ID: 6372943
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....

Nic;o)
0
 

Author Comment

by:mario_34
ID: 6373648
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?

0
 
LVL 54

Accepted Solution

by:
nico5038 earned 150 total points
ID: 6373805
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!

Nic;o)
0
 

Author Comment

by:mario_34
ID: 6374007
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] .

0
 
LVL 54

Expert Comment

by:nico5038
ID: 6374064
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.

Nic;o)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Report that will show table changes 7 48
Direct Mail software 4 46
sort Time by AM and PM in query 2 21
How autopopulate number field with 2 different criteria 9 51
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now