doCmd.OpenReport - Multiple Where Conditions

[jml9012]

Hello Everyone,

I am currently making a form that using combo boxes to filter a query for a report. I am using doCmd to open the report using two where conditions. The where conditions get their values from the combo box drop down list. As you can see below, I am attempting to open the report "FORM x" with multiple argument where conditions joining them with AND. However when I run this command I get a type mismatch error. I have tried isolating the issue by only using one where condition at a time and have found that filtering by date only or by vendorID only works perfectly fine. But when I try to combine the two where statements into one so I can filter by both parameters at once I receive a type mismatch error.

Works

    DoCmd.OpenReport "FORM x", acViewPreview, , " [Date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

Works

    DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "'"

Doesn't Work

    DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "'" And " [Date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

Any help at all would be extremely appreciative.

Thank you
Josh

[pbaldy]

The double quotes surrounding And are causing a problem.

[rzw0wr]

Hope I can keep the quotes straight.

DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "'" And " [Date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

Docmd.OpenForm,"Formx",AcPreview,,"VendorID =" & me.cboVendorID & "AND # [Date] # BETWEEN # '" & me.cboFrom & "' AND # '" & me.cboTo & "#'"

Numbers do NOT use any quotes.

Text-Date need quotes.

Hope this is something close.
I am not sure what data type [Date] is the # may not be needed.
Summary, Don't quote VendorID, Single quote the text-date variables.


Dale

[pbaldy]

Not sure which is supposed to be the correct version Dale; they're both wrong. The data types can be derived from the OP.

[rzw0wr]

Thanks Paul.

Can you tell me where I went wrong.

I have a terrible time with quotes if the statement if very complex.


Dale

[pbaldy]

Like I said, the only thing wrong with the code Josh posted was the double quotes on either side of And (the first And). It's a common mistake. You have two strings that work independently. You know you have to join them with And. The common mistake Josh made is not removing the quotes that ended the first string, since you no longer want to end that string, and the quotes that started the second string. So the correct string:

DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "' And [Date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

[rzw0wr]

Thanks Paul.

I see I could use mucho work on my quotes, Still.

Thanks,
Dale

[pbaldy]

Plus you had # around the field name, # and ' in a couple of places instead of one or the other. Don't worry, at some point it just all falls into place.

[rzw0wr]

I hope so.
I have gotten a grasp on about everything I have learned so far but these cotton picked quotes.

EDIT: I figured after I posted the # around the date field was wrong.

Thanks again for fixing it.

Dale

[TomChandler]

Quotes can be a pain. An alternative is to use Chr(34). I also prefer to use explicit notation when writing code. You might try this:

DoCmd.OpenReport _
ReportName:="FORM x", _
View:=acViewPreview, _
WhereCondition:="[VendorID] = " & Chr(34) & Me.cboVendorID & Chr(34) _
& " AND [Date] Between #" & Me.cboFrom & "# AND #" & Me.cboTo & "#"

[ssanfu]

Not to mention that "Date" as a field name is a terrible idea!!