Appending more than one filter to a report

[Jamescdawson]

I have created a report which is filtered using the following expression. The dates are entered on a "Report Selector" form and the expression picks up the dates from the two unbound fields txtFrom and txtTo on that From. However, the report returns all records in the report between the dates (as you would expect) - but I want to be able to report on a single record. (Customer name). I therefore want to add a filter after the date filter (below)




DoCmd.OpenReport "rptIncomingQSplit", acViewPreview, , "[Date Collected] BETWEEN #" & Format(Me.txtFrom, "mm/dd/yyyy") & "# AND #" & Format(Me.txtTo, "mm/dd/yyyy") & "#"

I understand that I can append another filter after the date filter but I don't know how and I don't know the syntax.

Ideally, I want to "Report Selector" form to have 3 elements.... Date From , Date TO and Customer Name which I would ideally like to select from a list in a Combo box. I already have the date selection fields set up. I have created a Combo box into which I select the customer name, but I can't get that 3rd element into my DoCmd.

Can anyone help ?

Jimbo

[pbaldy]

You keep building a string. If the field is text:

"[Date Collected] BETWEEN #" & Format(Me.txtFrom, "mm/dd/yyyy") & "# AND #" & Format(Me.txtTo, "mm/dd/yyyy") & "# AND CustomerName = '" & Me.CustomerName & "'"

[Jamescdawson]

Hi Paul... Long time No posts... Thanks for offering a solution...

I tried your string but I'm still falling over on the syntax...
The name of the Combo Box on the report selector form is <<txtCustomer>> Into that Combobox I select the "Known As" (That's the short name of the customer)

So I'm trying....

DoCmd.OpenReport "rptIncomingQSplit", acViewPreview, , "[Date Collected] BETWEEN #" & Format(Me.txtFrom, "mm/dd/yyyy") & "# AND #" & Format(Me.txtTo, "mm/dd/yyyy") & "# AND [KnownAs] = '" & Me.txtCustomer & "'"

But its not working. I'm not getting a failure or syntax error, it simply generates an empty report.

I'm not understanding the use of the ' and the "

Jimbo

[pbaldy]

Hi Jimbo! Well, the quotes look like this at their simplest:

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

If that's a combo, I'd wonder if the bound column returns the expected value. Can you post the db here?

[Jamescdawson]

Hi Paul.

Thanks... Got it... I had an extra # in there on a non date filter.

All now working

Thanks again

Jimbo

[pbaldy]

Happy to help!