Query for quotes, is this possible?

  • Thread starter Thread starter Julia S.
  • Start date Start date
J

Julia S.

I am trying to find all records that contain a double
quote. I am attempting to use this statement:
Like "*"*" but it is illegal. I tried to use the
backslash before the middle quote so it would be taken as
a literal but that is not working either. Does Access
not allow me to search for all records that contain a
double quote?

Julia
 
I am trying to find all records that contain a double
quote. I am attempting to use this statement:
Like "*"*" but it is illegal. I tried to use the
backslash before the middle quote so it would be taken as
a literal but that is not working either. Does Access
not allow me to search for all records that contain a
double quote?

Julia
Click to expand...

Julia,
Sure it does. Double up the quotes ""

Like "*""*"
 
That worked, but only for records containing 2 double
quotes: "". I need to find all records containing any
quotes at all. I already found all single quotes and
fixed them, now I need to find the double quotes...
 
Like "*""*"

should match values containing any double quotes, not just values containing
two consecutive double quotes.

The reason that there are two consecutive double quotes in the Like
expression is that double quotes are normally the string delimiter. Two
consecutive double quotes tells Like that, instead of marking the end of the
Like expression (as double quotes normally would), you want to include
double quotes (specifically, one double quotation mark character) in the
Like expression itself.
 
That worked, but only for records containing 2 double
quotes: "". I need to find all records containing any
quotes at all. I already found all single quotes and
fixed them, now I need to find the double quotes...
 
Your logic makes perfect sense! I just wish I could make
Access see it too. I'm using 2002 with SP-2, if that
matters. I am able to find the records with a single
double quote one at a time by using Find, but I've over
500,000 records to query, so Find is not really an
option. Hmmm, I wonder what the problem is? Thanks for
trying, I really do appreciate it!
 
How about:

Like '*"*'



Julia S. said:
Your logic makes perfect sense! I just wish I could make
Access see it too. I'm using 2002 with SP-2, if that
matters. I am able to find the records with a single
double quote one at a time by using Find, but I've over
500,000 records to query, so Find is not really an
option. Hmmm, I wonder what the problem is? Thanks for
trying, I really do appreciate it!
Click to expand...
 
Try this in the criteria field using a build:
InStr(1,[Table name]![Field Name],Chr(34),1)>"0"
In this example the field being searched for (") is called
[Table name]![Field Name]. The Chr() function converts a
decimal value to a character, in this case a decimal 34 is
the (") character.

So the Instr command is set (in the above example)to
start in the 1st position of the string/character variable
refered to as [Table Name]![Field Name} search for a (" or
Char(34)) using a text comparison (1). If you find one
return a number greater than zero that indicates the
characters position. Not finding the character in the
search would return 0 value. Here is a web site that has
the ASCII character values:
http://www.cs.net/lucid/ascii.htm
For more help on these functions use the "Build" function,
open the built in functions, high light the function you
are interested in and select help. Some functions don't
have help just a picture of what a help screen looks like.
In those cases try the closest look command or refer to a
Vbasic manual.
Good luck,
Hunter
 
Back
Top