SQL query expression - syntax error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Janis in Minnesota - 05 Feb 2006 05:10 GMT

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! :)

Reply to this Message

Marshall Barton - 05 Feb 2006 06:26 GMT

>Can someone help me with this SQL line of code?
>
[quoted text clipped - 13 lines]
>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 & """"

Signature

Marsh
MVP [MS Access]

Reply to this Message

Janis in Minnesota - 05 Feb 2006 15:00 GMT

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

> >Can someone help me with this SQL line of code?
> >
[quoted text clipped - 18 lines]
>
> " . . . WHERE . . . textfield = """ & variable & """"

Reply to this Message

Janis in Minnesota - 05 Feb 2006 15:02 GMT

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

> >Can someone help me with this SQL line of code?
> >
[quoted text clipped - 18 lines]
>
> " . . . WHERE . . . textfield = """ & variable & """"

Reply to this Message

Marshall Barton - 05 Feb 2006 16:08 GMT

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

Signature

Marsh
MVP [MS Access]

>Oh I'm so dumb sometimes!! I GOT IT!!! Sorry for that last post. The
>string should read:
[quoted text clipped - 30 lines]
>>
>> " . . . WHERE . . . textfield = """ & variable & """"

Reply to this Message