Openreport with date filter

[Sally*]

Hi, I am self taught on Access and VBA so there are still big holes in my basic knowledge.

I have a form with a date field [invoicedate] and a button to open a report with just the page(s) relating to that date. I have tried several options and they all open the entire report. my latest code (kindly suggested by another forum) is:


stDocName = "daily sales report"
DoCmd.OpenReport stDocName, acPreview, "[reportdate]='" & Me![INVOICE DATE] & "'", , acWindowNormal

but this still opens the entire report.

I have also tried the long coding suggested in several threads by Allen Browne but I think this is more for filtering between two dates rather than a single date.

Any help would be gratefully appreciated?

Sally

 

[Trevor G]

Welcome to the Form,

Is the report control source based on a query if so you can set the criteria in the query to use the field on the form as the filter and it will resolve your issue.

 

[pbaldy]

First, see here for the syntax required for a date field:

http://www.baldyweb.com/wherecondition.htm

Also, I think you have that in the wrong position (filter vs wherecondition).

 

[Sally*]

Hi, thanks for the link. I finally had chance to try this out and I am still getting the whole doc open.

stDocName = "daily sales report"
DoCmd.OpenReport stDocName, acPreview, "[reportdate]=#" & Me.[INVOICE DATE] & "#", , acWindowNormal

 

[pbaldy]

You appear to have the where condition in the filter argument's position.

 

[Bryan]

You appear to have the where condition in the filter argument's position.

Syntax:
DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

You need another comma after acPreview and to remove the comma after your WhereCondition. This will move your WhereCondition to the proper position.

 

[Sally*]

Thanks very much, that sorted it. All this hassle for one mis-placed comma, doh!!

 

[Sally*]

Hi all,

stDocName = "daily sales report"
DoCmd.OpenReport stDocName, acPreview, , "[invoice date]=#" & Me.[INVOICE DATE] & "#", acWindowNormal

Thanks to your help I solved this problem...or so I thought!!

This code has been working fine all month but it is now not working with the new month - I just get a blank report open.

I don't know if it's relevent but the only other date related thing I have is in the report query which is:

Expr1: DateDiff("m",[invoice date],Date())
<=3

Also, I am in the UK so use dd/mm/yy

I have gone back through my forms and now see that the code will also not work for previous months, just month 6. I have removed the code and the whole report opens, with all dates.

Any ideas??

Thanks

 

[vbaInet]

Use:

DoCmd.OpenReport stDocName, acPreview, , "[invoice date]=" & Me.[INVOICE DATE], "\#mm/dd/yyyy\#"), acWindowNormal

... as-is

 

[Sally*]

Thanks for the help but this is giving me a compile error: syntax error.

There is an rogue bracket at the end of "\#mm/dd/yyyy\#". I'm getting an error if I either remove it or put the ( in front, like this:

("\#mm/dd/yyyy\#")

so I don't know where the ( should be.

 

[vbaInet]

Oops... I was typing it on here and forgot to include the Format( part:

DoCmd.OpenReport stDocName, acPreview, , "[invoice date]=" & Format(Me.[INVOICE DATE], "\#mm/dd/yyyy\#"), acWindowNormal

 

[Sally*]

Perfect!!

Many thanks

 

[vbaInet]

You're welcome!