help with cleaning up data in a field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeannie S - 20 Dec 2004 12:57 GMT

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

Reply to this Message

Brendan Reynolds - 20 Dec 2004 14:45 GMT

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

Signature

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.

> One of my fields can contain data that looks like this:
>
[quoted text clipped - 10 lines]
>
> Thanks for all your help. Jeannie

Reply to this Message

Jeannie S - 20 Dec 2004 15:38 GMT

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

> I answered this on the 17th, in the microsoft.public.access newsgroup. Did
> you miss my response, or do you need clarification?
[quoted text clipped - 13 lines]
> >
> > Thanks for all your help. Jeannie

Reply to this Message

Duane Hookom - 20 Dec 2004 15:48 GMT

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

Signature

Duane Hookom
MS Access MVP
--

>I missed it. I still don't see that reply. Could you re-advise? Thanks
>so
[quoted text clipped - 19 lines]
>> >
>> > Thanks for all your help. Jeannie

Reply to this Message

Brendan Reynolds - 20 Dec 2004 16:10 GMT

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

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

Signature

>I missed it. I still don't see that reply. Could you re-advise? Thanks
>so
[quoted text clipped - 19 lines]
>> >
>> > Thanks for all your help. Jeannie

Reply to this Message

Jack MacDonald - 20 Dec 2004 15:48 GMT

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:
>
[quoted text clipped - 10 lines]
>
>Thanks for all your help. Jeannie

**********************
jackmacMACdonald@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

Reply to this Message