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??????
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)