[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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



0
jmarenghi
Asked:
jmarenghi
  • 5
  • 4
1 Solution
 
JonMnyCommented:
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 & "'")
0
 
jmarenghiAuthor Commented:
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

0
 
JonMnyCommented:
Try somthing like this

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


more info
http://allenbrowne.com/casu-17.html
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
cquinnCommented:
Me.txtLOC = DLookup("[LOC]", "[qryFRMInventory]", "[part_id]=" & Chr(34) & Me.cmbPART_id & Chr(34))

0
 
jmarenghiAuthor Commented:
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
0
 
JonMnyCommented:
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 & """")
0
 
jmarenghiAuthor Commented:
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
0
 
JonMnyCommented:
to make the single quote work you need to use double quotes


"[part_id]="""  & cmbpartVal & """")
0
 
JonMnyCommented:
I entered your data into my table to test and this worked.

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("[x]", "[Query_Entity331]", "[x]= """ & part & """")


just make sure that there are three "  before your & cmbpartval and  4 " after
0
 
jmarenghiAuthor Commented:
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

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now