Link to home
Start Free TrialLog in
Avatar of jmarenghi
jmarenghi

asked on

DLookUp my string has qutoes

I'm doing a simple DLookup in VB for an access database.
It worksfine unless the lookup value has quotes in it.
Some of the company's part numbers have single or double quotes for inches or feet respectively.
(not a good idea, but this company's database has been around a while)

I get a runtime error 3075 syntax error when the combobox value has quotes in it.

Me.txtLOC = DLookup("[LOC]", "[qryFRMInventory]", "[part_id]='" & Me.cmbPART_id & "'")

in this case the actual part_id is NYWIRSCREEN9.5"X1000'MESHEPOXY

How do i deal with random ' & " in my strings.

Thanks
JDM



Avatar of JonMny
JonMny

you can escape the " and '

cmbPart=Replace(Me.cmbPART_id , Chr(34), Chr(34) & Chr(34))
cmbPart=Replace(cmbPart ,"'","''")
Me.txtLOC = DLookup("[LOC]", "[qryFRMInventory]", "[part_id]='" & cmbPart & "'")
Avatar of jmarenghi

ASKER

Thanks JonMny...

This got rid of the error, but now the DlookUp does not function.
I mean that the me.txtLOC value is not set to the dlookup value. Just leaves it null.

So I set cmbPart variable to another textBox just to see what wa in it.
The replace funtion is adding the proper quotes, but i guess the DLookUp function can't find a match with the extra " and '.

Thanks
JDM

Try somthing like this

    =DLookup("City", "Customers", "CompanyName = """ & [CompanyName] & """")


more info
http://allenbrowne.com/casu-17.html
Me.txtLOC = DLookup("[LOC]", "[qryFRMInventory]", "[part_id]=" & Chr(34) & Me.cmbPART_id & Chr(34))

Thanks for the help, but still no luck.

I think that I'm in it here pretty deep. After querying the database of the company's some 14000 parts, I find that about 1000 of them have " or ' in them. There is no pattern to where and how often the characters will appear in the string.

Any more guidance is appreciated.
Thanks
JDM
Okay Looks like you need to handle the ' and double quotes separatly.


If InStr(Me.cmbPART_id, Chr(34)) Then
part = Replace(Me.cmbPART_id, Chr(34), Chr(34) & Chr(34))
Else
part = Me.cmbPART_id
End If

    Me.txtloc = DLookup("[LOC]", "[qryFRMInventory]", "[part_id]= """ & part & """")
Thanks again...

I have tried so many combinations that I'm confusing myself :)
Anyway, i've tried all the suggestions, but I can't seem to get where i need be. I've tried combining the DLookUP criiteria with single and double quotes with mixed results

Here is the method  I am currently using:

Dim cmbpartVal As String

cmbpartVal = Replace(Me.cmbPART_id.Value, Chr(34), Chr(34) & Chr(34))
cmbpartVal = Me.cmbPART_id.Value

Me.txtLOC = DLookup("[LOC]", "[qryFRMInventory]", "[part_id]='" & cmbpartVal & "'")

 Here are the results:
1/2"                   one double quote       ---> works
1.25"X3"X16"            multiple double quotes       ---> works
20'X.50X.025             one single quote      ---> broken
34'X2.375'X.028            multiple single quotes      ---> broken
28'3"X.50X.020            one single and double      ---> broken
12'10.5"X1"X.035'                           combonations of both      ---> broken

The code hadles any combo of double quotes. any time a single quote is involved in the string it can't handle it.

I have applied all of the above examples including the IF InStr statement... I get varying results. Some work some don't. I need a catchall for all the combinations I've indicated.
I'll keep plugging.

Again, any help is appreciated.
Thanks
JDM
to make the single quote work you need to use double quotes


"[part_id]="""  & cmbpartVal & """")
ASKER CERTIFIED SOLUTION
Avatar of JonMny
JonMny

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks JonMny...

Got it.!

Your IF statement combined with concatenating the DLookUp criteria using the double quote method is the way.

I was having problems because In my mess of code (i'm not a good programmer) I did not comment out a sections that was reverting my variable back to the combobox value before conversion.

Thanks
JDM