Calculated fields to split data from one field to two

J

Jay

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
 
O

Ofer Cohen

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)
 
O

Ofer Cohen

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],InStr([Market]," IOWA")-1)
 
J

John Spencer

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]," ")))

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

Jay

By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
Click to expand...

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]," ")))
Click to expand...

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


John Spencer said:
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]," ")))

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

Jay said:
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
Click to expand...
Click to expand...
 
J

John Spencer

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.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jay said:
By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
Click to expand...

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]," ")))
Click to expand...

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


John Spencer said:
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]," ")))

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

Jay said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Jay

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.
Click to expand...

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

Jay

John Spencer said:
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.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jay said:
By the way I don't see how your expression avoid returning CAS IOWA from
PFFS CAS IOWA
Click to expand...

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]," ")))
Click to expand...

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


John Spencer said:
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]," ")))

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

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
Click to expand...
Click to expand...
Click to expand...
 
Top