Message> in query expression <expression>. (Error

ybg1 · #1 (**permalink**) July 12th, 2007, 12:57 PM

I've created a Report Generator in Access VBA that uses a Filter Command.
When I use data that includes a single quot I get an error 3075.
Otherwise data with no single quot works fine.
How can I get over this problem?

Here is the code:

' Build criteria string from lstAuthor listbox

    For Each varItem In Me.lstAuthor.ItemsSelected
        strAuthor = strAuthor & ",'" & Me.lstAuthor.ItemData(varItem) _
        & "'"
    Next varItem

robzyc · #2 (**permalink**) July 13th, 2007, 03:26 AM

ybg1,

The Single Quote basically tells the SQL Parser that the String has finished,
and the characters following that will cause it to fall over.

To avoid this, when working with Strings, replace with TWO single quotes.
e.g (for your code)

Code:

' Build criteria string from lstAuthor listbox
    Dim selAuthor as String
    selAuthor = Replace$(Me.lstAuthor.ItemData(varItem),"'", "''")
    For Each varItem In Me.lstAuthor.ItemsSelected
        strAuthor = strAuthor & ",'" & selAuthor & "'"
    Next varItem

I hope this helps,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Ashfaque · #3 (**permalink**) July 14th, 2007, 06:05 AM

You may avoide this using following method too.

This is just an example I used in my db. you may please replace the variables of your choice.

Set rst1 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= '" & Replace(CboSearchItem, Chr(39), Chr(39) & Chr(39)) & "'")

Hope this will help,

With kind regards
Ashfaque

robzyc · #4 (**permalink**) July 14th, 2007, 06:39 AM

Ashfaque,

Thats the same thing!? (Well we could be pedantic and argue the toss over execution speeds of Replace/Replace$, and 3x function calls to Chr - but lets not!! =) )

One point I did fail to mention is to make sure you use Nz when referencing Control values, or you will get a Invalid Use of Null error thrown if the value has not been set.

Regards,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

ybg1 · #5 (**permalink**) July 15th, 2007, 02:16 AM

Hi!

Thank you Rob and Ashfaque for your suggestions.

It was very helpful.

Regards,

Ybg

robzyc · #6 (**permalink**) July 15th, 2007, 05:42 AM

Ybg,

No problem, happy to be of help!

Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Need to download code?

Navigation