Trimming off special characters

#1 (**permalink**) 28-Jul-2006, 08:19 AM

I have a field with special characters before and after the info I want
returned. For example:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11253-trimming-off-special-characters.html
[["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!

*

#2 (**permalink**) 28-Jul-2006, 08:19 AM

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"

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11253

"CMcGrann" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11253
news:B17A6CB5-355C-4EBD-88D3-6824ECB343B1@microsoft.com...
>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!

#3 (**permalink**) 28-Jul-2006, 08:19 AM

On Mon, 12 Jun 2006 06

01 -0700, CMcGrann wrote:

> I have a field with special characters before and after the info I want
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11253
> 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;
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11253
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail