Help needed to filter a report

[Zaeed]

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]

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]

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]

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]

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]

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]

*sigh*

works now

cheers for that link, should prove useful since i pull my date from a activeX calendar..

damn americans breaking my code

 

[pbaldy]

Doesn't the universe revolve around us?

 

[Zaeed]

Yeah but it revolves backwards