searching for the character " (1 Viewer)

[ghodges]

I have a database that I rescently imported a lot of text. every time it imported the symbol (") it has altered the data by wrapping it with extra "

eg drill bit 3/8" is now "drill bit 3/8""

There about 7000 of these little data corruptions so it is impractical to fix manually ( really big guess about the 7000!)

my problem is i cant search for a "

I have tried the usual tricks :

Like "*["]*"

and

Like "*" &["] & "*"

and

Instr(1,[FieldNameHere], """) > 0

None of these have been very successful


has anybody go an idea how to search for a "

 

[Kryst51]

Try Chr(34), I know that is used for " in vba code.

 

[boblarson]

There might be a way to use replace but I'm going to throw out a quick function for you and you can just drop it in and call it and be done with it:

Function FixProblemQuotes(strDataToFix As String) As String
 
 
   If Left(strDataToFix, 1) = "" Then
      strDataToFix = Mid(strDataToFix, 2)
   End If
 
   If Right(strDataToFix, 2) = """" Then
      strDataToFix = Left(strDataToFix, Len(strDataToFix) - 1)
   End If
 
FixProblemQuotes = strDataToFix
 
End Function

There, just call that function by passing the Field for your Update query.

Do the initial tests on a COPY of the data.