SQL query expression - syntax error

[Guest]

Can someone help me with this SQL line of code?

Dim strSQL As String
Dim lImporterID As Long
Dim strReference As String

strReference = Me.EntryCustomerReferenceNbr
lImporterID = Me.EntryImporterNbrID

strSQL = "SELECT Entry.* FROM Entry WHERE Entry.EntryCustomerReferenceNbr =
" & strReference & " AND Entry.EntryImporterNbrID = " & lImporterID

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

I need to select an Entry with 2 criteria but I get an error when I run the
application. The error I receive is Run-time error '3464': Data type
mismatch in criteria expression.

Any help is GREATLY appreciated!

Thanks so much!

Janis in Minne-SNOW-ta!

 

[Marshall Barton]

Can someone help me with this SQL line of code?

Dim strSQL As String
Dim lImporterID As Long
Dim strReference As String

strReference = Me.EntryCustomerReferenceNbr
lImporterID = Me.EntryImporterNbrID

strSQL = "SELECT Entry.* FROM Entry WHERE Entry.EntryCustomerReferenceNbr =
" & strReference & " AND Entry.EntryImporterNbrID = " & lImporterID

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)

I need to select an Entry with 2 criteria but I get an error when I run the
application. The error I receive is Run-time error '3464': Data type
mismatch in criteria expression.

If a field is a Text type field, then the condition needs to
have the value enclosed in quotes:

" . . . WHERE . . . textfield = """ & variable & """"

 

[Guest]

I changed the SQL query string to this:
strSQL = "SELECT Entry.* FROM Entry WHERE
Entry.EntryCustomerReferenceNbr.textfield = " & strReference & " AND
Entry.EntryImporterNbrID = " & lImporterID

and I get an error still. How do I tell the query that the reference number
is a text field?

Thanks so much!

Janis

 

[Guest]

Oh I'm so dumb sometimes!! I GOT IT!!! Sorry for that last post. The
string should read:

strSQL = "SELECT Entry.* FROM Entry WHERE Entry.EntryCustomerReferenceNbr =
"" & strReference & "" AND Entry.EntryImporterNbrID = " & lImporterID

This should work, right? At least I get no errors. Now to see if it does
indeed find this information . . .

Thanks much! And - if you see something wrong with this string please let
me know.

Thanks again!

Janis

 

[Marshall Barton]

You still don't have the quotes right.

The key is that when you need to use a quote withn a quoted
string, you have to have to use two quotes to indicate that
the inner quote is not the end of the string.

A procedure that you can use to help you construct a quoted
string is to start with the final result and look for any
quotes in the string. Where ever you have a quote in the
result make it two quotes. Then add a quote at the
beginning and end of that.

This is a confusing subject, so I'll try a couple of
examples to see if they can provide further insight.

If you want a string variable to contain:
She said "hello"
First, make each quote two:
She said ""hello""
and the assignment statement would be:
strvar = "She said ""Hello"""

If you want the string to be just:
"
First double the quote:
""
then add the quotes to the beginning and end so the
statement is:
strvar = """"

In your case, all that translates to:

strSQL = "SELECT Entry.* FROM Entry " & _
"WHERE Entry.EntryCustomerReferenceNbr = """ & _
strReference & """ AND Entry.EntryImporterNbrID = " & _
lImporterID