parse query results 1

[s0ccerdude]

Is there a way to parse query results so that the contents of one field can be distributed across 2 or more fields?

In one of my tables, I have a text field that I would like to split up across multiple query fields so that I can sort on the newly created fields. For instance, in the field that I've labeled "Action", each recordset currently contains data in the following format:

Do Pass / Short Debate Revenue Committee; 009-003-000

I would like to build a query that automatically splits this field into 5 fields

Field 1= Do Pass
Field 2= Short Debate Revenue Committee
Field 3= 009
Field 4= 003
Field 5= 000

In MS Excel, I know that I could split the "Action" column by using the "text to columns" functions by using the "/", ";" and "-" symbols as delimiters.

Is there a more elegant way to do this in a standing Access query?

Any help would be much appreciated. Thank you

 

[dhookom]

There are string functions in Access such as:
Instr()
Left()
Right()
Mid()
and others. You would be best off trying to figure this on your own. You seem to have a good knowledge of expressions and Access isn't much different from Excel with functions and expressions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[s0ccerdude]

Well, you pointed me in the right direction dhookom. After much trial and error I've come close to figuring this out on my own. Thank you.