Create a Variable from First 4-5 chars of a existing value ??

  • Thread starter kev100 via AccessMonster.com
  • Start date
K

kev100 via AccessMonster.com

In a current table, the values in a particular field are all items like:

MD45 Tuesday PM
or
TX341 Friday AM
or
FL12 Monday PM


...in other words...2 parts seperated by a space. The first part is always a
State Abbrv. followed by either a 2 or 3 char. number. That first part will
always be 4-5 characters long (or...everthing before the first Space).

I'm needing to create a varible that is the First Part of the existing value..
..in other words...the variable would need to be (from the above examples)...

MD45
or
TX341
or
FL12


I'm kind of familiar with LEFT command. Would using some version be going in
the right direction?

Something like:

PartVal: LEFT([CurrentVal],4)
-or-
PartVal: LEFT([CurrentVal],5)

...but.....how could it dectect WHICH one to use (if the above are even
correct)?

...maybe something to search for the first space?


Any help appreciated.
Thanks!
 
J

Jeff Boyce

Take a look at the InStr() function. This will give you a way (Access a
way) to determine where the space is. Your new expression would look
something like:

Left([YourField],InStr([YourField]," ")-1)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

KARL DEWEY

You should not store data like this, use separate fields.
Use this --
First Part: Trim(Left([YourFieldName],5))
 
Top