Type Mismatch

[tscotti]

Hi,

I'm getting a "Type Mismatch" error on the code below after adding the fields [ProductDescription] and [ProductColour] in the expression.

All three fileds are of the text type.

Dim strDocName As String
Dim strWhere As String

strDocName = "rptProducts"

strWhere = "[ProductID]='" & Me![ProductID] & "'" And "[ProductDescription]='" & Me![ProductDescription] & "'" And "[ProductColour]='" & Me![ProductColour] & "'"


DoCmd.OpenReport strDocName, acViewNormal, , strWhere​

Thanks, in advance, for your help.

 

[RuralGuy]

Too many double quotes!
strWhere = "[ProductID]='" & Me![ProductID] & "' And "[ProductDescription]='" & Me![ProductDescription] & "' And "[ProductColour]='" & Me![ProductColour] & "'"

 

[boblarson]

And if [ProductID] is not text, then get rid of the single quotes for it.

 

[tscotti]

Thanks RG and BobLarson for responding.

I removed the quotes as prescribed but am now getting "syntax error"!

 

[RuralGuy]

Please copy and paste your strWhere = "...
Into a post so we can see it. You can also do a
MsgBox StrWhere just after setting it to see what you have.

 

[boblarson]

I think, you could also be having problems due to naming.
You have:
"[ProductDescription]='" & Me![ProductDescription]
And Me![ProductDescription] actually references the field in the recordset and not the Me.ProductDescription (using the dot operator) for referencing the control. You should rename the control to txtProductDescription and then refer to
"[ProductDescription]='" & Me.txtProductDescription

This applies to any of the other fields you are using in the strWhere build.

 

[tscotti]

Here's the complete sub:

Private Sub cmdPrintRecord_Click()
On Error GoTo Err_cmdPrintRecord_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim strDocName As String
Dim strWhere As String

strDocName = "rptProducts"

strWhere = "[ProductID]='" & Me![ProductID] & "' And "[ProductDescription]='" & Me![ProductDescription] & "' And "[ProductColour]='" & Me![ProductColour] & "'"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere


Exit_cmdPrintRecord_Click:
Exit Sub

Err_cmdPrintRecord_Click:
MsgBox Err.Description
Resume Exit_cmdPrintRecord_Click

End Sub​

Originally, I just had the [ProductID] code and that worked fine.
Thanks again for your help.
Tony

 

[RuralGuy]

I see a extra " that you need to get rid of:

strWhere = "[ProductID]='" & Me![ProductID] & "' And "[ProductDescription]='" & Me![ProductDescription] & "' And [ProductColour]='" & Me![ProductColour] & "'"

And as Bob stated, if [ProductID] is numeric then no single quotes:

strWhere = "[ProductID] [COLOR="Red"]= " & Me![ProductID] & " And [/COLOR][ProductDescription]='" & Me![ProductDescription] & "' And  [ProductColour]='" & Me![ProductColour] & "'"

 

[tscotti]

It worked! Thanks RG!

I need to review syntax!

Regards,

Tony

 

[RuralGuy]

Glad we could help. Don't forget the MsgBox trick to see what you have created.