Search fields for quote symbols

  • Thread starter Thread starter Guest
  • Start date Start date
Hi Ray

Do you mean in a query?

Select FName from tblTest where
Instr(FName, Chr(34)) <> 0 or Instr(FName, Chr(39)) <> 0

Chr(34) is a double-quote and Chr(39) is a single-quote.

You could also use this expression to filter a form:

Me.Filter = "Instr(FName, Chr(34)) <> 0 or Instr(FName, Chr(39)) <> 0"
Me.FilterOn = True
 
Ray said:
how do I search the FName field in tblTest for quote symbols?
Click to expand...


Use a query criteria of LIKE "*'*"

or, in VBA code:
If somevariable Like "*'*"

or provide more information about what you need to do.
 
Thanks everyone. The select query works great, is there a way to change it
so it removes the data such as "butch" etc....

Graham Mandeno said:
Hi Ray

Do you mean in a query?

Select FName from tblTest where
Instr(FName, Chr(34)) <> 0 or Instr(FName, Chr(39)) <> 0

Chr(34) is a double-quote and Chr(39) is a single-quote.

You could also use this expression to filter a form:

Me.Filter = "Instr(FName, Chr(34)) <> 0 or Instr(FName, Chr(39)) <> 0"
Me.FilterOn = True

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ray Mead said:
how do I search the FName field in tblTest for quote symbols?
Click to expand...
Click to expand...
 
Hi Ray

You cannot do this in straight SQL (well, not easily!), but you can write a
VBA function to do the job.

Basically, you need to:
1. Find the position (p1) of the first quote (InStr)
2. Find the position (p2) of the next quote (InStr)
3. Form a new string from Left(str, p1-1) & Mid(str, p2+1)
4. Repeat until no more quotes found
5. Return the result

Rather than discarding the quoted data, you may wish to save it in a
different field (Nickname?)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Ray Mead said:
Thanks everyone. The select query works great, is there a way to change
it
so it removes the data such as "butch" etc....

Graham Mandeno said:
Hi Ray

Do you mean in a query?

Select FName from tblTest where
Instr(FName, Chr(34)) <> 0 or Instr(FName, Chr(39)) <> 0

Chr(34) is a double-quote and Chr(39) is a single-quote.

You could also use this expression to filter a form:

Me.Filter = "Instr(FName, Chr(34)) <> 0 or Instr(FName, Chr(39)) <>
0"
Me.FilterOn = True

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ray Mead said:
how do I search the FName field in tblTest for quote symbols?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top