Zaeed
11-09-2009, 06:14 PM
I've got a report that i'm trying to filter using two dates..
My form has two text fields that allow you to enter the dates, I then use the docmd.openReport Where clause to open the report based on the selected dates
"(((tbl_Change.Date_Start) Between " & Me.frm_Reports_Date1 & " And " & Me.frm_Reports_Date2 & " ))"
however my report is coming up blank..
Date_Start is in the Report
Any suggestions?
pbaldy
11-09-2009, 06:44 PM
Presuming it's a date/time field, the values need to be surrounded by "#". This type of thing:
http://www.baldyweb.com/wherecondition.htm
Zaeed
11-10-2009, 02:36 PM
hmm, this does strange things..
I have the WHERE clause of the docmd statement to be
"((tbl_Change.Date_Start) Between #" & Me.frm_Reports_Date1 & "# And #" & Me.frm_Reports_Date2 & "# )"
This opens up the report, but the records displayed are not the ones defined by the WHERE clause.. Either all records are shown, or no records are shown, and it randomly decides which of these two to display..
Am I correct in assuming that the Report should have a record source of a query? Is that how its meant to work?
Any ideas on what i'm doing wrong
pbaldy
11-10-2009, 02:45 PM
It can have a record source of a table or query; it doesn't matter to this code. I assume there's no filtering going on in the query? I just tested this and it worked fine:
DoCmd.OpenReport "rptTest", acPreview, , "ReqDateTime between #" & Me.Text1 & "# And #" & Me.Text3 & "#"
Can you post the db?
Zaeed
11-10-2009, 04:19 PM
No luck..
If I put the date between 1/11/09 and 11/11/09 I should get only 1 record, but I get all of them..
This is the WHERE clause that the report is getting, seen from the reports properties
(Date_Start Between #26/11/2009# And #1/11/2009#)
pbaldy
11-10-2009, 04:34 PM
In code, Access likes dates in US format. It's considering that to be Jan 1 through Nov 11. One link on the topic:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210069
Zaeed
11-10-2009, 04:41 PM
*sigh*
works now :p
cheers for that link, should prove useful since i pull my date from a activeX calendar..
damn americans breaking my code :p
pbaldy
11-10-2009, 05:10 PM
Doesn't the universe revolve around us? :p :rolleyes:
Zaeed
11-10-2009, 05:57 PM
Yeah but it revolves backwards :p :D