Data type mismatch in criteria expression 1

[hwkranger]

SELECT DISTINCTROW USYSCheckInformation.VID, USYSCheckInformation.[Chk #], USYSLetterInformation.Date, USYSCheckInformation.dateReq FROM USYSCheckInformation INNER JOIN USYSLetterInformation ON USYSCheckInformation.[Invoice Number] = USYSLetterInformation.[Letter Number] WHERE (((USYSCheckInformation.[Chk #])=" & checknum & &quot);"

This says: Data type mismatch in criteria expression

what's wrong with this???? I can't realy figure it out!

Checknum is a string variable.... Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[ajdesalvo]

Is [chk #] a text field, then:

Number] WHERE (((USYSCheckInformation.[Chk #])=" & checknum & &quot);"

would be:

Number] WHERE (((USYSCheckInformation.[Chk #])='" & checknum & "'));"

The other issue could be:
USYSCheckInformation.[Invoice Number] = USYSLetterInformation.[Letter Number]

if either one of these field types are not the same.

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hwkranger]

You're definately uber - can you please explain to me why i needed the single quotes? and why the double quotes are enclosed in the single quotes?

I understand the double quotes, it's because it's a string, but I don't understand why the closing of the string with the concatonation has to be inside the single quotes.

Thankyou alot for helping me with this problem. Please do explain why it was required (so I don't make the same mistake again ;-) Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[ajdesalvo]

You are creating a sql string that looks like this for a text variable:

"Select * from [TableName] WHERE [FieldName] = '1234';"

So when building the sql string you need the single quotes within the double quotes:

"Select * from [TableName] WHERE [FieldName] = '" & yourvariable & "';"

If the field type in a number, then you do not need the single quotes:

"Select * from [TableName] WHERE [FieldName] = 1234;"

would be:

"Select * from [TableName] WHERE [FieldName] = " & yourvariable & ";"

I hope that helped!


Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hwkranger]

Thanks! I can't believe I'm such a bone-head, thankyou for taking time to explain that. It's part of the String!!

Cruz'n and Booz'n always.
This post shows what little I do at work.