Problem with filtering report

[Sunny]

On user interface form I am asking transaction date range, and I would like
to set these dates as filter condition on filter property of report.
In open event of Report following code works fine.

Me.Filter = "TrxDate BETWEEN #" & Forms("Rpt1").txtStartDate & "# AND #" &
Forms("Rpt1").txtEndDate & "#"

but
"TrxDate BETWEEN #" & Forms("Rpt1").txtStartDate & "# AND #" &
Forms("Rpt1").txtEndDate & "#"
on Filter property of report doesnt work. It gives me error, runtime error
'3075', "Invalid use of '.', '!', or '()' in query expression.

Can anyone help meto find out what am i missing?

Note: I dont want to use filter on open event becuase this report can be
used as sub report.

Thanks.

 

[Larry Linson]

Looks to me as if you already found your own solution --
set the Filter from the Open event. There are limitations
to the expressions acceptable in a property that do not
apply to code... when you use the code, you have
substituted the values of the date controls before
inserting it into the Filter.

As for me, I'd rather create the entire SQL statement and
replace the RecordSource. It can be significantly more
efficient to let the database engine select only the
needed records, than to bring them all and then filter.

Larry Linson
Microsoft Access MVP

-----Original Message-----
On user interface form I am asking transaction date range, and I would like
to set these dates as filter condition on filter property of report.
In open event of Report following code works fine.

Me.Filter = "TrxDate BETWEEN #" & Forms

("Rpt1").txtStartDate & "# AND #" &