Calculated fields to split data from one field to two

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 08 Nov 2007 15:52 GMT

Hello-

I am trying to take data from field [Market] and create two fields called
[PLAN_TYPE_NM] and [MARKET_NM].
The data is text and looks like this:
Market
PFFS IOWA
PFFS CAS IOWA
PFFS IOWA
PFFS IOWA

What I'm trying to do is make MARKET_NM Iowa, and everything else
PLAN_TYPE_NM. I've got IOWA separated by using,
MARKET_NM: Right([Market],InStr([Market]," ")-1)

My problem is I can't seem to get PFFS CAS into it's own field. What screws
me up is the fact the field could contain either PFFS, or PFFS CAS. I have
looked at multiple posts and everything I've tried using Left() and Mid()
doesn't seem to give me what I'm looking for.

Any help would be greatly appreciated as I'm sure I'm missing something
simple.
Thanks,

Jay

Reply to this Message

Ofer Cohen - 08 Nov 2007 16:27 GMT

Instead of the Right function try the Replace to replace "IOWA" with nothing
to remove it

Replace([Market],"IOWA","")

That incase you always want to remove "IOWA"

*****
If that not the case and there are different names and not just "IOWA" try
using InStrRev to get the space before the last string

Right([Market],InStrRev([Market]," ")-1)

*****
Or, In the InStr look for " IOWA" with space

Right([Market],InStrRev([Market]," IOWA")-1)

Signature

Good Luck
BS"D

> Hello-
>
[quoted text clipped - 21 lines]
>
> Jay

Reply to this Message

Jay - 08 Nov 2007 16:44 GMT

> Instead of the Right function try the Replace to replace "IOWA" with nothing
> to remove it
>
> Replace([Market],"IOWA","")

This worked perfectly! The state name will change, but I can just replace
"IOWA" with "Insert State Name Here".
I had a feeling I was making it more difficult than it had to be.

Thanks again for your help, and very quick response!

-Jay

> Instead of the Right function try the Replace to replace "IOWA" with nothing
> to remove it
[quoted text clipped - 39 lines]
> >
> > Jay

Reply to this Message

Ofer Cohen - 08 Nov 2007 16:27 GMT

Signature

Good Luck
BS"D

> Hello-
>
[quoted text clipped - 21 lines]
>
> Jay

Reply to this Message

John Spencer - 08 Nov 2007 16:30 GMT

Try the following. If your version of Access does not support InStrRev you
will get an error message

Trim(Left([Market],InStrRev(" " & [Market]," ")))

By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
You might try the following to get just the market
Trim(Mid([Market],InStrRev(" " &[Market]," ")))

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Hello-
>
[quoted text clipped - 23 lines]
>
> Jay

Reply to this Message

Jay - 08 Nov 2007 16:56 GMT

> By the way I don't see how your expression avoid returning CAS IOWA from
> PFFS CAS IOWA

I used Right([Market],InStr([Market]," ")-1) and it returned only "IOWA"
from either PFFS CAS IOWA, or PFFS IOWA.

> You might try the following to get just the market
> Trim(Mid([Market],InStrRev(" " &[Market]," ")))

This worked as well, is there a right or wrong here? Or just two different
ways to get at the same data?

Thanks again for assistance,

Jay

> Try the following. If your version of Access does not support InStrRev you
> will get an error message
[quoted text clipped - 33 lines]
> >
> > Jay

Reply to this Message

John Spencer - 08 Nov 2007 17:08 GMT

Coincidence.
Your INSTR returns 5 - the position of the first space in PFSS CAS IOWA and
then you get the 5 right most characters

Try it with
PFFS CAS NEBRASKA
and you will get RASKA returned.

InstrRev returns the position of the last space.

So your method will give you incorrect results.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>> By the way I don't see how your expression avoid returning CAS IOWA from
>> PFFS CAS IOWA
[quoted text clipped - 53 lines]
>> >
>> > Jay

Reply to this Message

Jay - 08 Nov 2007 17:31 GMT

> Coincidence.
> Your INSTR returns 5 - the position of the first space in PFSS CAS IOWA and
[quoted text clipped - 7 lines]
>
> So your method will give you incorrect results.

Absolutely right! Thank you for catching that and saving me a headache down
the road.
Thanks again for the knowledge.

Jay

> Coincidence.
> Your INSTR returns 5 - the position of the first space in PFSS CAS IOWA and
[quoted text clipped - 64 lines]
> >> >
> >> > Jay

Reply to this Message