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

[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!

 

[Ken Snell \(MVP\)]

This should work for all cases:

PartVal: Trim(Left([CurrentVal],5))

 

[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

 

[Guest]

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