Quotation mark problem!

Martyh

Registered User.
Local time
Today, 03:27
Joined
May 2, 2000
Messages
196
People,

Can anyone think of any way that I could put this question without incurring a Runtime error (3075)? --- "St. John's NL" is actually a variable and not a constant and it is in "qryMarketFrombufCarRentalConvert".

?DLookup("[F1]", "qryMarketFrombufCarRentalConvert", "[F1]= " & Chr(39) & "St. John's NL" & Chr(39))

TIA

Marty H.
 
So f1 is the field name in the query and St John's NL is the value in the field you are looking for?
 
try using chr(34) instead.
 
Hi Ken,

Yes that's right.

I narrowed down the problem to "St. John's NL" which happens to be in loc1, the variable name.
 
So did you get it working?

Something is fundamentally wrong with the way you're using the dlookup() function; You're asking to return the value of the field you're putting the criteria on - ?!
 
Ken,

I have no problem with the dLookup function; it has worked 3934 times before!

it has do with the pesky single quotation mark. I've tried all the permutation and combination of quote including chr()

What I haven't tried is to clense the data beforehand!! I am attempted that right now!
Have you got any other thoughts?

Marty
 
Are you saying that loc1 contains both single and double quotes?

Chris
 
I think the way round this is to assign ' to a string varable say Strquote and use this instead in your string, I seem to remember Pat Hartman discussing this along time ago.

you end up with something like "St. John" & strquote & "s NL"

Brian
 
Sorry to beat a dead horse...

DLookup("[F1]", "qryMarketFrombufCarRentalConvert", "[F1]= " & Chr(39) & "St. John's NL" & Chr(39))

In this sample you've given, you are wanting the value in the field 'F1', from a recordset named 'qryMarketFrombufCarRentalConvert', where the value of the field 'F1' equals 'St. John's NL'. Is this correct?

??? Am I missing something?
 
I agree Ken, but he has said that it has given him what he wants 3000+ times before. :confused:

Brian
 
Ken,

No -- you are quite correct! Only to add your statement "St. John's NL" is one instance of loc1. The dlookup statement works for all other instances. The problem is that I've got over 300,000 records to use the dlookup on ... and probably more. I thought that there would be another method otther than dlookup, that I could use in a similar way.

Brian and ecawilkinson,

The problem is that I need something else to do the job of dlookup, as it will not lookup "St. John's NL" and others like it!

It looks like my immediate soln is to pre-cleanse that data... however in the long run I will still be looking for a solution!

Marty
 
If you have the patience I'd like to help figure this out. I don't think the apostrophe thing will not be that hard figure out. But in order to do that I need to fully understand the context in which you're trying to use the dlookup().

So may I assume that if you use a value that doesn't contain an apostrophe it works as you like? Can you give me an exact example?
 
F/U:

Looks like you're plugging this straight into the immediate window - ?

If so, what is the exact code that you use in your application?
 
I've got the patience ... just not the time -- right now!

Your right again -- this code snippet is taken from my program ... and i was using it to show the problem.

Tomorrow (or monday) I'll have more time...

Marty
 
Whilst i don't understand why you are looking for the value of F1 by giving it, the code below shows 3 ways of coding for the ' , the second is the best as it is easily used in a function when you do not want to hard code the values, as illustrated also in the code.

Brian



Public Sub basgetf2()

Dim strsquote As String
Dim strdq As String
strsquote = Chr$(39)
strdq = Chr$(34)
str1 = "St John"
str2 = "s Hosp"
strsearch = "St John's Hosp"

varx1 = dlookup("[f2]", "table1", "[f1]=" & strdq & str1 & strsquote & str2 & strdq)
MsgBox varx1
varx2 = dlookup("[f2]", "table1", "[f1]=" & strdq & strsearch & strdq)
MsgBox varx2
varx3 = dlookup("[f2]", "table1", "[f1]=" & strdq & "St John" & strsquote & "s Hosp" & strdq)
MsgBox varx3

End Sub
Public Function fgetf2(f1) As String

Dim strdq As String
strdq = Chr$(34)
strsearch = f1
fgetf2 = dlookup("[f2]", "table1", "[f1]=" & strdq & strsearch & strdq)

End Function
 
I haven't really been following this thread but I have the same problem often.

In code, I use:
mystr = "where mycol = '" & replace(myvariable,"'","''") & "'"

When testing with a value with a single quote:
where mycol = 'St. John''s NL'

Sorry, I never use DLOOKUP and haven't experimented with it. I'm thinking the concept is the same though and I would try using replace to change any instance of ' to '' (2 tick marks).

I hope this helps.
 
i think the critical thing is how do you expect to be able to search for something as random as a street name

if a user is entering this into a text field they could be entring the text as

st john's NL
st johns NL
st john's
st johns
or even
saint johns

how can you be sure you are doing the search correctly, with a randomish text string
 
Hi Guys,

I'm back to this ... I must explain "St. John's, NL" is coming from a drop down box --- there is no problem ever "misspelling" --- the problem is with specifically with dlookup! Is there another way to to lookup a name and make sure that this name is in the list (not the drop-down list, but another list)!

Hey wait a minute !!
I can open the query in a dynaset type -- "qryMarketFrombufCarRentalConvert",
and use the FindFirst method.

Thanks for your patience but I've got the answer --- although its a bit more involved!

Marty
 
There was another thread where I asked the question and Mile-O-something suggested that I use triple quotes:

Code:
"""O'Reilly's"""
.
 

Users who are viewing this thread

Back
Top Bottom