Search problem searching for the " Char

[gearcam]

I use the normal method of searching like :

' Check for LIKE Supplier ref
If Me.searchorderref > "" Then
varWhere = varWhere & "[Supplier Ref] LIKE """ & Me.searchorderref & """ AND "
End If

no problems but some fields contain data and the " char, uses want to search for a dimention of a compt ie 2.5"
The " charachter causes an syntax error in query anybody know a way around this ?

Thanks

Steve

 

[tehNellie]

replacing the double quotes in your SQL syntax for Single quotes should help.

ie

"WHERE mycolumn = '" & me.txtbox & "'"

In theory you should also be able to replace a single instance of a special character with a double, ie " becomes "" using a function such as replace(), but the way VBA/Access deals with double quotes had me tearing my hair out trying to test it.

 

[gearcam]

I this is the original line

varWhere = varWhere & "[Description] LIKE ""*" & Me.searchdesc1 & "*"" AND "

Me.searchdesc1 = say 2.5"

I tried this

varWhere = varWhere & "[Description] LIKE " * " & Me.searchdesc1 & " * " And "

but i get an error 13 type mismatch

 

[LPurvis]

That's not quite what you were advised.

varWhere = varWhere & "[Description] LIKE '*" & Me.searchdesc1 & "*' And "

But that requires you guessing what character it's best to delimit against.
(I find that single quotes are more common in strings that double).

So the general methodology is to escape the delimitation value

varWhere = varWhere & "[Supplier Ref] LIKE """ & Replace(Me.searchorderref, """", """""") & """ AND "or
varWhere = varWhere & "[Supplier Ref] LIKE """ & Replace(Me.searchorderref, vbCrLf, vbCrLf & vbCrLf) & """ AND "

 

[gearcam]

i must get some new glasses i missed the ' with the "

This works great

varWhere = varWhere & "[Description] LIKE '*" & Me.searchdesc1 & "*' And "

Many thanks