Trimming off special characters

[Guest]

I have a field with special characters before and after the info I want
returned. For example:
[["010XQ12345"
["010XQ987654"
I only want what is between the quotes and remove all other characters. Can
I do an update query to do this???

Thanks!

 

[Al Camp]

If you're saying that every string has a (") just before the text you want, and a (")
just after the text you want.
Run an update query against that field (ex. name = [MyStr])...
=Mid([MyStr],InStr([MyStr],Chr(34))+1,Len([MyStr])-InStr([MyStr],Chr(34))-1)

I tested against [["1234ABCD" and ["1234ABCD" and [[[["1234ABCD"

 

[fredg]

I have a field with special characters before and after the info I want
returned. For example:
[["010XQ12345"
["010XQ987654"
I only want what is between the quotes and remove all other characters. Can
I do an update query to do this???

Thanks!

If your data can look like this..
[["010XQ12345"avbd
and you want
010XQ12345

and you have Access 2000 or newer, then copy and paste the following
code into a module:

Public Function RemoveExcessChr(FieldIn As String) As String

Dim intX As Integer
Dim strString As String
strString = Mid(FieldIn, InStr(FieldIn, Chr(34)) + 1)
strString = Left(strString, InStrRev(strString, Chr(34)) - 1)
RemoveExcessChr = strString

End Function

Next, create an Update query:
Update YourTable Set YourTable.FieldName =
RemoveExcessChr([FieldName]) Where YourTable.FieldName is not null;