Openreport with date filter (1 Viewer)

Sally* · May 29, 2012

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 · May 29, 2012

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 · May 30, 2012

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* · Jun 14, 2012

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 · Jun 14, 2012

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

Bryan · Jun 14, 2012

pbaldy said:
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* · Jun 18, 2012

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

Sally* · Jul 4, 2012

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 · Jul 4, 2012

Use:

Code:

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

... as-is

Sally* · Jul 4, 2012

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 · Jul 4, 2012

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

Code:

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

Sally* · Jul 4, 2012

Perfect!!

Many thanks

vbaInet · Jul 4, 2012

You're welcome!

Openreport with date filter (1 Viewer)

Sally*

Registered User.

Trevor G

Registered User.

pbaldy

Wino Moderator

Sally*

Registered User.

pbaldy

Wino Moderator

Bryan

Registered User.

Sally*

Registered User.

Sally*

Registered User.

vbaInet

AWF VIP

Sally*

Registered User.

vbaInet

AWF VIP

Sally*

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread