help with cleaning up data in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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,
You can go to google.com and enter "TRK#790152346464" to find Brendan's
reply.
 
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
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
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.
 
Back
Top