Can’t filter a report between date range (2000)

Topic

New Reply

Hi,

I’m trying to open a report between a date range. I’ve used code similar to code given to me from the lounge in the past but keep getting an error message that but I keep getting an error message asking for an end of statement. I know I’m missing a comma or bracket or something but am not sure where it goes. The code works fine until I add in the date related information. The code I’m using is as follows:

DoCmd.OpenReport “rptBatchInvoice”, acViewPreview, , “AccountID = ” & Me.lstBillingHistory & ” _
” And ([DateWorked] Between #” & Me.StartDate & “# And #” & Me.EndDate & “#)”

Me.StartDate and Me.EndDate are both unbound controls on the form that are used to filter the report.

Thanks,
Leesha

Reply | Quote

Replies

This works:
DoCmd.OpenReport “rptBatchInvoice”, acViewPreview, , “AccountID = ” & Me.lstBillingHistory & _
” And ([DateWorked] Between #” & Me.StartDate & “# And #” & Me.EndDate & “#)”

Notice that I have left off the trailing ” on the first line.

Reply | Quote

Thanks Pat, that worked! Now can you tell me why?

Thanks,
Leesha

Reply | Quote

In any command where you use quotes you must have an even number of them else it cannot figure out what you are trying to do.

Reply | Quote

I guess that makes sense. So, how do I know when I need quotes??? I’ll tell you, for me its more trial and error and if I had my way I’d leave them out totally.

Leesha

Reply | Quote

Quotes are used to surround strings.

Let us take a look at your posted example:

DoCmd.OpenReport “rptBatchInvoice”, acViewPreview, , “AccountID = ” & Me.lstBillingHistory & ” _
” And ([DateWorked] Between #” & Me.StartDate & “# And #” & Me.EndDate & “#)”

The first pair of quotes surround the report name rptBatchInvoice, the next 2 quotes surround the text ‘AccountID = ‘. Then you made the error, your next 2 quotes are virtually contiguous (the 1st is at the end of the first line and the 2nd is at the start of the 2nd line, then from there it’s all downhill.
Hope this explains it reasonably well.

Reply | Quote

It’s a great explanation, whether I’ll ever totally “get it” or not remains to be seen. The whole “string” thing is beyond me!

Leesha

Reply | Quote