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'MESHE POXY
How do i deal with random ' & " in my strings.
Thanks
JDM
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'MESHE
How do i deal with random ' & " in my strings.
Thanks
JDM
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
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
=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))
ASKER
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
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 & """")
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 & """")
ASKER
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.Valu e, 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
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.Valu
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 & """")
"[part_id]=""" & cmbpartVal & """")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
cmbPart=Replace(Me.cmbPART
cmbPart=Replace(cmbPart ,"'","''")
Me.txtLOC = DLookup("[LOC]", "[qryFRMInventory]", "[part_id]='" & cmbPart & "'")