Parsing data in a simple query

[ausmoran]

I am attempting to separate the contents of a field in a simple query in Access 2003. The contents of this particular field consist of three, four or five leading alpha characters, followed by five to nine numeric characters. Is there a way to set up the criteria in such a way that I will only retrieve the ALPHA characters or the NUMBERIC characters from this field?

Thanks in advance for your assistance.

Austin

 

[PHV]

You may try something like this:
Alpha: IIf([field name] Like '????[!0-9]#*', Left([field name],5), IIf([field name] Like '???[!0-9]#*', Left([field name],4), Left([field name],3)))

Digit: IIf([field name] Like '????[!0-9]#*', Mid([field name],6), IIf([field name] Like '???[!0-9]#*', Mid([field name],5), Mid([field name],4)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ausmoran]

Thanks so very much! I hadn't thought about using IIF statements to parse things, so this is a great idea.

Once again you guys have come to my rescue!

Have a great day and live well!

Austin