help with cleaning up data in a field

[Jeannie S]

One of my fields can contain data that looks like this:

TRK#790152346464
T#K0300481272

I want to lose anything to the left ANDincluding the # sign. I want the
data to be:

790152346464
K0300481272

Can anyone advise?

Thanks for all your help. Jeannie

 

[Brendan Reynolds]

I answered this on the 17th, in the microsoft.public.access newsgroup. Did
you miss my response, or do you need clarification?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Jeannie S]

I missed it. I still don't see that reply. Could you re-advise? Thanks so
much.

 

[Duane Hookom]

Jeannie S,
You can go to google.com and enter "TRK#790152346464" to find Brendan's
reply.

 

[Jack MacDonald]

In a field in a Select query, you can enter the following formula:

p: InStr([fldName],"#")
you can apply a criterion to this formula (>0) to select only the
fields where the "#" character occurs

Then create another field:
truncated: Mid([fldname],InStr([fldName],"#"))

Of course, replace FldName with the name of the appropriate field.

Run this query to verify that you've entered the formula correctly and
that it generates the results that you want. Once you are satisfied
with the results, you can change the query into an update query, and
replace the FldName contents with the value calculated

One of my fields can contain data that looks like this:

TRK#790152346464
T#K0300481272

I want to lose anything to the left ANDincluding the # sign. I want the
data to be:

790152346464
K0300481272

Can anyone advise?

Thanks for all your help. Jeannie

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Brendan Reynolds]

Here's a link to the reply in the Google archive ...

http://groups-beta.google.com/group/microsoft.public.access/msg/0e3f9bd8e466faae

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.