String Delimiters in Find

[Trevor]

Anybody help please.

I am using a linked DB4 table in my Access database. I
need to lookup (using FindFirst etc.) an ID field that may
contain either single quotes, double quotes or both. I am
OK when the string contains double quotes, I use
somethging like

strcriteria ="ID = '" & (field) & "'"
rst.FindFirst strcriteria

But of coure, this fails when field contains a single
quote. Is there any way round this? I vaguely remember
that another delimeter can be used, but can't remember
what it is (but just to screw me up, I bet that character
is also in my search string.

TIA
Trevor

 

[Ron Weiner]

The easiest way I have found is double up all single quotes by passing the
variable through a FixQuotes Function. Put the function below in a Module
and you can use it from anywhere in your app.

Example -> strcriteria ="ID = '" & FixQuotes(SomeVariableORField) & "'"

The function below will work with any version of access.


Public Function FixQuotes(strQuoted As String) As String
' Purpose Double up single quotes for use in a Sql query
Dim i As Integer, strOut As String

strOut = ""
For i = 1 To Len(strQuoted)
If Mid(strQuoted, i, 1) = Chr(39) Then
strOut = strOut & Chr(39)
End If
strOut = strOut & Mid(strQuoted, i, 1)
Next
FixQuotes = strOut
End Function


Ron W

 

[Trevor]

Thanks Ron,
I may be able to do what you suggest, but I'll have to
have a good stiff look at my data to find out whether
doing this will cause other problems such as creating,
duplicate IDs.
I still think there is another delimiter that can be used
though.

Trevor

 

[Roger Carlson]

Well, if you don't want to use FixQuotes (which will work with ANY string in
your database if you save it in a general module), you can replace the
apostrophe (') with TWO quotes:

strcriteria ="ID = """ & (field) & """"

 

[Ron Weiner]

Roger

You are absolutely correct about replacing the single Quote with a pair of
Double Quotes, but all that will do is to make the problem he has now with
embedded Single Quotes to embedded Double Quotes. What happens if he wants
to do something like:

strcriteria ="ID = """ & (field) & """"

where the value in field is '19" monitor' or 'the "Smiths" house'

No matter how you shake it or bake it sometime, somewhere he will get an
error when the conditions are right (or wrong depending on ones point of
view). To have it work ALL of the time he needs to look at a method of
"Escaping" either the single or double quote depending on what he decides to
use as a delimiter.

Ron W

 

[Roger Carlson]

Sorry, I missed the bit in the first post about the field having both
apostrophes and quotes. Still, he seemed reluctant to use your code, so I
would suggest the following:

strcriteria = "[ID] = " & Chr$(34) & (field) & Chr$(34)

This will avoid the problem altogether.

On my website, there is a complete discussion of using embedded quotes that
I excerpted from an out-of-print book by Ken Getz. It has the best
explanation I have ever seen. You can find it here:

http://www.rogersaccesslibrary.com/knowledge.html

 

[Ron Weiner]

Roger

Try this in the immediate window

field = "37"" TV" : strcriteria = "[ID] = " & Chr$(34) & (field) &
Chr$(34) : ? strcriteria
When there is an embedded Double Quote in the variable field then the
resulting expression
[ID] = "37" TV"
which is a bad expression.

On The Other Hand
field = "37"" TV": strcriteria = "[ID] = " & Chr$(39) & (field) &
Chr$(39): ? strcriteria
which results in
[ID] = '37" TV'
OR
field = "37"""" TV": strcriteria = "[ID] = " & Chr$(34) & (field) &
Chr$(34): ? strcriteria
which results in
[ID] = "37"" TV"
Will work OK

Now I grant you it is far more likely that you will run into an embedded
Single quote more often than a Double quote, but it only takes one 19"
monitor or 12.2" brake rotor to ruin a perfectly wonderful day with a
support call. The only way to avoid getting that support call is to double
up on the character that you choose to use as the Sql delimiter be it a
single or double quote.

Ron W

Sorry, I missed the bit in the first post about the field having both
apostrophes and quotes. Still, he seemed reluctant to use your code, so I
would suggest the following:

strcriteria = "[ID] = " & Chr$(34) & (field) & Chr$(34)

This will avoid the problem altogether.

On my website, there is a complete discussion of using embedded quotes that
I excerpted from an out-of-print book by Ken Getz. It has the best
explanation I have ever seen. You can find it here:

http://www.rogersaccesslibrary.com/knowledge.html

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Roger

You are absolutely correct about replacing the single Quote with a pair of
Double Quotes, but all that will do is to make the problem he has now with
embedded Single Quotes to embedded Double Quotes. What happens if he wants
to do something like:

strcriteria ="ID = """ & (field) & """"

where the value in field is '19" monitor' or 'the "Smiths" house'

No matter how you shake it or bake it sometime, somewhere he will get an
error when the conditions are right (or wrong depending on ones point of
view). To have it work ALL of the time he needs to look at a method of
"Escaping" either the single or double quote depending on what he

decides

to
use as a delimiter.

Ron W

string

 

[Trevor]

Wow, that stirred things up a bit didn't it.

To enlarge on my problem. I have two attached tables that
are generated by a db4 database one is a list of names and
the other is a list of addresses. In the names database
there is an ID that points to an address in the addresses
table.
The address ID for a person is a four character code which
(invariably) starts with a double double quote, a
character and then maybe a single quote. e.g ""Z' or "'a"
(all the quotes are in the ID) etc. Only a few of the IDs
have both single and double quotes.

The DB4 database seems to be case sensitive, as an address
ID for a person may be ""z" but when he changes address
the new address will have the ID ""Z". I have sorted the
case bit by using strcompare on the find, but am still
having probs with the quotes. I don't feel inclined to
change the single quotes in either table's ID field to a
double quote (although this may be the only answer).

Any further conversations on this matter will be more than
welcome..
Thanks chaps

Trevor

-----Original Message-----
Roger

Try this in the immediate window

field = "37"" TV" : strcriteria = "[ID] = " & Chr$(34) & (field) &
Chr$(34) : ? strcriteria
When there is an embedded Double Quote in the variable field then the
resulting expression
[ID] = "37" TV"
which is a bad expression.

On The Other Hand
field = "37"" TV": strcriteria = "[ID] = " & Chr$(39) & (field) &
Chr$(39): ? strcriteria
which results in
[ID] = '37" TV'
OR
field = "37"""" TV": strcriteria = "[ID] = " & Chr$(34) & (field) &
Chr$(34): ? strcriteria
which results in
[ID] = "37"" TV"
Will work OK

Now I grant you it is far more likely that you will run into an embedded
Single quote more often than a Double quote, but it only takes one 19"
monitor or 12.2" brake rotor to ruin a perfectly wonderful day with a
support call. The only way to avoid getting that support call is to double
up on the character that you choose to use as the Sql delimiter be it a
single or double quote.

Ron W

Sorry, I missed the bit in the first post about the field having both
apostrophes and quotes. Still, he seemed reluctant to use your code, so I
would suggest the following:

strcriteria = "[ID] = " & Chr$(34) & (field) & Chr$(34)

This will avoid the problem altogether.

On my website, there is a complete discussion of using

embedded quotes
that

I excerpted from an out-of-print book by Ken Getz. It has the best
explanation I have ever seen. You can find it here:

http://www.rogersaccesslibrary.com/knowledge.html

Quote with a pair
of problem he has now
with

What happens if he
wants

depending on what he
decides

to will work with ANY
string

.

 

[Ron Weiner]

Trevor

Try using the code I put in the origional reply in a general module and use
it thusly.

strcriteria ="ID = '" & FixQuotes(field) & "'"

That will use a single quote as the delimiter and double up any single
quotes that happen to be in field. Find should like this. If you have
trouble post your code so we can see what is going on.

As an aside is there some reason that you don't just write a Sql statement
that joins both tables on these fields and work with the results. Something
like:

SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.ID = b.ID

The quotes will not come into play and you will have everything all nice an
neat.

Ron W

Wow, that stirred things up a bit didn't it.

To enlarge on my problem. I have two attached tables that
are generated by a db4 database one is a list of names and
the other is a list of addresses. In the names database
there is an ID that points to an address in the addresses
table.
The address ID for a person is a four character code which
(invariably) starts with a double double quote, a
character and then maybe a single quote. e.g ""Z' or "'a"
(all the quotes are in the ID) etc. Only a few of the IDs
have both single and double quotes.

The DB4 database seems to be case sensitive, as an address
ID for a person may be ""z" but when he changes address
the new address will have the ID ""Z". I have sorted the
case bit by using strcompare on the find, but am still
having probs with the quotes. I don't feel inclined to
change the single quotes in either table's ID field to a
double quote (although this may be the only answer).

Any further conversations on this matter will be more than
welcome..
Thanks chaps

Trevor

-----Original Message-----
Roger

Try this in the immediate window

field = "37"" TV" : strcriteria = "[ID] = " & Chr$(34) & (field) &
Chr$(34) : ? strcriteria
When there is an embedded Double Quote in the variable field then the
resulting expression
[ID] = "37" TV"
which is a bad expression.

On The Other Hand
field = "37"" TV": strcriteria = "[ID] = " & Chr$(39) & (field) &
Chr$(39): ? strcriteria
which results in
[ID] = '37" TV'
OR
field = "37"""" TV": strcriteria = "[ID] = " & Chr$(34) & (field) &
Chr$(34): ? strcriteria
which results in
[ID] = "37"" TV"
Will work OK

Now I grant you it is far more likely that you will run into an embedded
Single quote more often than a Double quote, but it only takes one 19"
monitor or 12.2" brake rotor to ruin a perfectly wonderful day with a
support call. The only way to avoid getting that support call is to double
up on the character that you choose to use as the Sql delimiter be it a
single or double quote.

Ron W

Sorry, I missed the bit in the first post about the field having both
apostrophes and quotes. Still, he seemed reluctant to use your code, so I
would suggest the following:

strcriteria = "[ID] = " & Chr$(34) & (field) & Chr$(34)

This will avoid the problem altogether.

On my website, there is a complete discussion of using

embedded quotes
that

I excerpted from an out-of-print book by Ken Getz. It has the best
explanation I have ever seen. You can find it here:

http://www.rogersaccesslibrary.com/knowledge.html

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org



Roger

You are absolutely correct about replacing the single

Quote with a pair
of

Double Quotes, but all that will do is to make the

problem he has now
with

embedded Single Quotes to embedded Double Quotes. What happens if he
wants
to do something like:

strcriteria ="ID = """ & (field) & """"

where the value in field is '19" monitor' or 'the "Smiths" house'

No matter how you shake it or bake it sometime, somewhere he will get an
error when the conditions are right (or wrong depending on ones point of
view). To have it work ALL of the time he needs to look at a method of
"Escaping" either the single or double quote

depending on what he
decides

to
use as a delimiter.

Ron W

Well, if you don't want to use FixQuotes (which will work with ANY
string
in
your database if you save it in a general module), you can replace the
apostrophe (') with TWO quotes:

strcriteria ="ID = """ & (field) & """"


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


Anybody help please.

I am using a linked DB4 table in my Access database. I
need to lookup (using FindFirst etc.) an ID field that may
contain either single quotes, double quotes or both. I am
OK when the string contains double quotes, I use
somethging like

strcriteria ="ID = '" & (field) & "'"
rst.FindFirst strcriteria

But of coure, this fails when field contains a single
quote. Is there any way round this? I vaguely remember
that another delimeter can be used, but can't remember
what it is (but just to screw me up, I bet that character
is also in my search string.

TIA
Trevor

.

 

[Trevor]

Ron,
Thanks for persevering with my problem. I tried your
original FixQuotes,and although it seemed to suppress the
string error, it did not find the ID.

I have actually joined the two tables with a query, and
sorted out the problem with IDs in thge second table being
the same (but different case e.g. ""zZ and ""ZZ), by using
strcompare (binary) after the find, and it is in this
strcompare that I am having the problem. I havn't had time
to try out your SQL yet, as I'm not now at home where the
problem is. Will it return 'duplicates' if the IDs are as
above, or will it sort out the case problem as well. If I
can't get either your SQL or FixQuotes working tonight,
I'll post a few more details and bits of the code that I
am using at present.

Thanks a lot for your time,
Trevor

-----Original Message-----
Trevor

Try using the code I put in the origional reply in a general module and use
it thusly.

strcriteria ="ID = '" & FixQuotes(field) & "'"

That will use a single quote as the delimiter and double up any single
quotes that happen to be in field. Find should like this. If you have
trouble post your code so we can see what is going on.

As an aside is there some reason that you don't just write a Sql statement
that joins both tables on these fields and work with the results. Something
like:

SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.ID = b.ID

The quotes will not come into play and you will have everything all nice an
neat.

Ron W

Wow, that stirred things up a bit didn't it.

To enlarge on my problem. I have two attached tables that
are generated by a db4 database one is a list of names and
the other is a list of addresses. In the names database
there is an ID that points to an address in the addresses
table.
The address ID for a person is a four character code which
(invariably) starts with a double double quote, a
character and then maybe a single quote. e.g ""Z' or "'a"
(all the quotes are in the ID) etc. Only a few of the IDs
have both single and double quotes.

The DB4 database seems to be case sensitive, as an address
ID for a person may be ""z" but when he changes address
the new address will have the ID ""Z". I have sorted the
case bit by using strcompare on the find, but am still
having probs with the quotes. I don't feel inclined to
change the single quotes in either table's ID field to a
double quote (although this may be the only answer).

Any further conversations on this matter will be more than
welcome..
Thanks chaps

Trevor

-----Original Message-----
Roger

Try this in the immediate window

field = "37"" TV" : strcriteria = "[ID] = " & Chr$(34)

&
(field) &

Chr$(34) : ? strcriteria
When there is an embedded Double Quote in the variable field then the
resulting expression
[ID] = "37" TV"
which is a bad expression.

On The Other Hand
field = "37"" TV": strcriteria = "[ID] = " & Chr$(39) & (field) &
Chr$(39): ? strcriteria
which results in
[ID] = '37" TV'
OR
field = "37"""" TV": strcriteria = "[ID] = " & Chr$(34) & (field) &
Chr$(34): ? strcriteria
which results in
[ID] = "37"" TV"
Will work OK

Now I grant you it is far more likely that you will run into an embedded
Single quote more often than a Double quote, but it

only
takes one 19"

monitor or 12.2" brake rotor to ruin a perfectly wonderful day with a
support call. The only way to avoid getting that

support
call is to double

up on the character that you choose to use as the Sql delimiter be it a
single or double quote.

Ron W

Sorry, I missed the bit in the first post about the field having both
apostrophes and quotes. Still, he seemed reluctant

to
use your code, so I

would suggest the following:

strcriteria = "[ID] = " & Chr$(34) & (field) & Chr$(34)

This will avoid the problem altogether.

On my website, there is a complete discussion of

using
embedded quotes

that
I excerpted from an out-of-print book by Ken Getz.

It
has the best

explanation I have ever seen. You can find it here:

http://www.rogersaccesslibrary.com/knowledge.html

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org



Roger

You are absolutely correct about replacing the

single
Quote with a pair

of
Double Quotes, but all that will do is to make the problem he has now
with
embedded Single Quotes to embedded Double Quotes. What happens if he
wants
to do something like:

strcriteria ="ID = """ & (field) & """"

where the value in field is '19" monitor' or 'the "Smiths" house'

No matter how you shake it or bake it sometime, somewhere he will get an
error when the conditions are right (or wrong depending on ones point of
view). To have it work ALL of the time he needs to look at a method of
"Escaping" either the single or double quote depending on what he
decides
to
use as a delimiter.

Ron W

Well, if you don't want to use FixQuotes (which will work with ANY
string
in
your database if you save it in a general

module),
you can replace the

apostrophe (') with TWO quotes:

strcriteria ="ID = """ & (field) & """"


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


Anybody help please.

I am using a linked DB4 table in my Access database. I
need to lookup (using FindFirst etc.) an ID

field
that may

contain either single quotes, double quotes or both. I am
OK when the string contains double quotes, I use
somethging like

strcriteria ="ID = '" & (field) & "'"
rst.FindFirst strcriteria

But of coure, this fails when field contains a single
quote. Is there any way round this? I vaguely remember
that another delimeter can be used, but can't remember
what it is (but just to screw me up, I bet that character
is also in my search string.

TIA
Trevor








.

.

 

[Guest]

Ron
I must have done somethging wrong in my original test of
your code. I tried it again this morning and it worked
fine, just like you said it would. 3 out of 10 for me for
for not persevering yesterday, or perhaps it was because I
was a bit fed up with the whole thing, and a new day has
given my typing finger new inspiration. Or perhaps it's
because there is a transit of Venus this morning. Who
knows, but thanks again for your help and the inputs from
Roger.
Trevor

-----Original Message-----
Trevor

Try using the code I put in the origional reply in a general module and use
it thusly.

strcriteria ="ID = '" & FixQuotes(field) & "'"

That will use a single quote as the delimiter and double up any single
quotes that happen to be in field. Find should like this. If you have
trouble post your code so we can see what is going on.

As an aside is there some reason that you don't just write a Sql statement
that joins both tables on these fields and work with the results. Something
like:

SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.ID = b.ID

The quotes will not come into play and you will have everything all nice an
neat.

Ron W

Wow, that stirred things up a bit didn't it.

To enlarge on my problem. I have two attached tables that
are generated by a db4 database one is a list of names and
the other is a list of addresses. In the names database
there is an ID that points to an address in the addresses
table.
The address ID for a person is a four character code which
(invariably) starts with a double double quote, a
character and then maybe a single quote. e.g ""Z' or "'a"
(all the quotes are in the ID) etc. Only a few of the IDs
have both single and double quotes.

The DB4 database seems to be case sensitive, as an address
ID for a person may be ""z" but when he changes address
the new address will have the ID ""Z". I have sorted the
case bit by using strcompare on the find, but am still
having probs with the quotes. I don't feel inclined to
change the single quotes in either table's ID field to a
double quote (although this may be the only answer).

Any further conversations on this matter will be more than
welcome..
Thanks chaps

Trevor

-----Original Message-----
Roger

Try this in the immediate window

field = "37"" TV" : strcriteria = "[ID] = " & Chr$(34)

&
(field) &

Chr$(34) : ? strcriteria
When there is an embedded Double Quote in the variable field then the
resulting expression
[ID] = "37" TV"
which is a bad expression.

On The Other Hand
field = "37"" TV": strcriteria = "[ID] = " & Chr$(39) & (field) &
Chr$(39): ? strcriteria
which results in
[ID] = '37" TV'
OR
field = "37"""" TV": strcriteria = "[ID] = " & Chr$(34) & (field) &
Chr$(34): ? strcriteria
which results in
[ID] = "37"" TV"
Will work OK

Now I grant you it is far more likely that you will run into an embedded
Single quote more often than a Double quote, but it

only
takes one 19"

monitor or 12.2" brake rotor to ruin a perfectly wonderful day with a
support call. The only way to avoid getting that

support
call is to double

up on the character that you choose to use as the Sql delimiter be it a
single or double quote.

Ron W

Sorry, I missed the bit in the first post about the field having both
apostrophes and quotes. Still, he seemed reluctant

to
use your code, so I

would suggest the following:

strcriteria = "[ID] = " & Chr$(34) & (field) & Chr$(34)

This will avoid the problem altogether.

On my website, there is a complete discussion of

using
embedded quotes

that
I excerpted from an out-of-print book by Ken Getz.

It
has the best

explanation I have ever seen. You can find it here:

http://www.rogersaccesslibrary.com/knowledge.html

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org



Roger

You are absolutely correct about replacing the

single
Quote with a pair

of
Double Quotes, but all that will do is to make the problem he has now
with
embedded Single Quotes to embedded Double Quotes. What happens if he
wants
to do something like:

strcriteria ="ID = """ & (field) & """"

where the value in field is '19" monitor' or 'the "Smiths" house'

No matter how you shake it or bake it sometime, somewhere he will get an
error when the conditions are right (or wrong depending on ones point of
view). To have it work ALL of the time he needs to look at a method of
"Escaping" either the single or double quote depending on what he
decides
to
use as a delimiter.

Ron W

Well, if you don't want to use FixQuotes (which will work with ANY
string
in
your database if you save it in a general

module),
you can replace the

apostrophe (') with TWO quotes:

strcriteria ="ID = """ & (field) & """"


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


Anybody help please.

I am using a linked DB4 table in my Access database. I
need to lookup (using FindFirst etc.) an ID

field
that may

contain either single quotes, double quotes or both. I am
OK when the string contains double quotes, I use
somethging like

strcriteria ="ID = '" & (field) & "'"
rst.FindFirst strcriteria

But of coure, this fails when field contains a single
quote. Is there any way round this? I vaguely remember
that another delimeter can be used, but can't remember
what it is (but just to screw me up, I bet that character
is also in my search string.

TIA
Trevor








.

.

 

[david epsom dot com dot au]

doubled up quotes, and starting/ending quotes, don't go
"into" your data, nor are they "used" by FindFirst.
It is just a way of writing a string so that (in this case)
Jet/Access understands what you mean:

You write 'Fred''s'

I see: Fred's
Jet sees: Fred's
Data is: Fred's

If you WANT to have doubled up quotes in your data, you
have to double them up too:

Fred''s

'Fred''''s'

Which would be done correctly by the posted code.

(david)

 

[Trevor]

david,
Thanks for that. I have learned quite a lot about Jet over
the last couple of days.
Ron's FixQuotes (elsewhere in this strand) fixed my
immediate problem. I can now find strings containing both
quotation marks and appostrophes, and sort out the case
problem that I had.

Thanks for the post.

Trevor