We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
2 days, 8 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sorting by date
Posted on 2001-08-10
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.Fi
(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.Fi
, 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??????
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.Fi
(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.Fi
, 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??????
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
-
4
- +2
12 Comments
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)
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)
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 ?
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 ?
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.
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.
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)
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)
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.
This comes out as #12-MAR-2001# - it is recognised as a valid date by Oracle, Access, SQL Server etc.
Hi,
i used the following code:
[MainForm_Subform].Form.Fi
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 used the following code:
[MainForm_Subform].Form.Fi
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.Fi
" AND " & Date2
Access will do the translation then for you.
When you have stringfields use:
[MainForm_Subform].Form.Fi
"# AND #" & Date2 & "#"
But then the format must be OK....
Nic;o)
BTW I would set the filter with a date-type field like:
[MainForm_Subform].Form.Fi
" AND " & Date2
Access will do the translation then for you.
When you have stringfields use:
[MainForm_Subform].Form.Fi
"# AND #" & Date2 & "#"
But then the format must be OK....
Nic;o)
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?
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?
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)
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)
Hi nico!
I finally solved the problem!
I used the following syntax:
[MainForm_Subform].Form.Fi
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] .
I finally solved the problem!
I used the following syntax:
[MainForm_Subform].Form.Fi
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.Fi
" 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)
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.Fi
" 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)
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s
ManageEngine
webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month2 days, 8 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.