date criteria for report from form

  • Thread starter Thread starter Peter Woodhead
  • Start date Start date
P

Peter Woodhead

Can anyone tell me why the following won't work when I use
a form to enter the dates - if I substitute the variables
dteStartDate and dteEdate with actual dates the report
opnes filtered to the dates

Dim strWhere As String
Dim dteStartDate As Date
Dim dteEDate As Date

dteStartDate = Me.txtBeginDate
dteEDate = Me.txtEndDate
strWhere = "qryMaterialsDue.OrdTDate BETWEEN
#dteStartDate# And #dteEDate#"
DoCmd.OpenReport "rptMaterialsDue", acViewPreview, ,
strWhere

any ideas?
 
The WhereCondition of the OpenReport is a string.
Concatenate the values of the variables into the string:

strWhere = "qryMaterialsDue.OrdTDate BETWEEN " & _
Format(Me.txtBeginDate, "\#mm\/dd\/yyyy\#") & " And " & _
Format(Me.txtEDate, "\#mm\/dd\/yyyy\#")
 
If you are getting all records in your report, make sure:
- you included the *2* commas before the WhereCondition;
- the report is not already open.

If you get no records, or the wrong records in the report:
- If txtBeginDate and txtEDate are unbound, set their Format property to
Short Date so Access understands the intented data type.
- If OrdTDate is a calculated field, wrap the calculation in CVDate(), e.g.:
OrdTDate: CVDate(DateAdd("d", 30, [OrderDate]))
Explanation:
http://members.iinet.net.au/~allenbrowne/ser-45.html
 
Thanks again but it was my fault I altered the format to
dd/mm/yy thinking it should reflect that in my unbound
control. When I put it back as you said it works just
fine.
-----Original Message-----
If you are getting all records in your report, make sure:
- you included the *2* commas before the WhereCondition;
- the report is not already open.

If you get no records, or the wrong records in the report:
- If txtBeginDate and txtEDate are unbound, set their Format property to
Short Date so Access understands the intented data type.
- If OrdTDate is a calculated field, wrap the calculation in CVDate(), e.g.:
OrdTDate: CVDate(DateAdd("d", 30, [OrderDate]))
Explanation:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks
I have tried this and the report opens but no filter is
applied!
any ideas And "
& _ when I
use
Click to expand...


.
Click to expand...
 
Back
Top