error 3071 on OpenReport command

[degras]

I have a form with three text boxes (one is for text and the other two are for dates) which are used to filter a report. I run the report via the following command but I get an error 3071:

DoCmd.OpenReport "CollectionInvoices", acViewPreview, , "Company = '" & _
Me.CompanyNameText & "' And InvoiceDate >= '" & Me.StartDateText & _
"' And InvoiceDate <= '" & Me.EndDateText & "'"

From Access help:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

Should I be using the "Between" function (syntax) ?



Thanks.

[pbaldy]

If InvoiceDate is a date/time field, the values need to be surrounded by # rather than '.

[NTC]

DoCmd.OpenReport "CollectionInvoices", acViewPreview, , "Company = " & _
Me.CompanyNameText & And "InvoiceDate >= " & #Me.StartDateText# & _
And "InvoiceDate <= " & #Me.EndDateText#

[pbaldy]

DoCmd.OpenReport "CollectionInvoices", acViewPreview, , "Company = " & _
Me.CompanyNameText & And "InvoiceDate >= " & #Me.StartDateText# & _
And "InvoiceDate <= " & #Me.EndDateText#

That won't even compile.

[degras]

Thanks. This did the trick:

DoCmd.OpenReport "CollectionInvoices", acViewPreview, , "Company = '" & _
Me.CompanyNameText & "' And InvoiceDate >= #" & Me.StartDateText & _
"# And InvoiceDate <= #" & Me.EndDateText & "#"

[pbaldy]

Happy to help.