Need help with a query.

[elmorro]

Hi all,
I have a field in an ACCESS table that allows the user to provide an address. The Street,City and State are all placed in the field and seperated with a comma. I would now like to break-up this field into three separate fields. Is there a query I could write or a function I could use to accomplish this?

Thanks in advance,
elmorro

 

[fneily]

Whoever designed the table didn't even do the first form of Normalization. The street, city and state should be separate fiels. Tell them to study access first before creating tables.
Can you supply an example of the field? Is the state two characters or spelled out? is the street and city separated by a comma? How about the city and state? etc.
Then someone out here can write you code or supply you with a function to parse out the data.
But, can you recreate the table the correct way? Or is there to much data?

 

[fneily]

You may want to look at the following:
thread700-879579

 

[elmorro]

Here is an example of value in the address field:

123 Elm Street, Los Angeles, CA.

What I want is the following:
Street City State
123 Elm Street Los Angeles CA.

elmorro

 

[Golom]

It's clunky but

Select 
Left([FieldName], Instr(1,[FieldName],",")-1) As [Street],

Mid([FieldName], Instr(1,[FieldName],",")+1,
    InstrRev([FieldName],",") -
    Instr(1,[FieldName],",")+1)               As [City], 

Mid([FieldName], InstrRev([FieldName],",")+1) As [State]

Assuming that every field has exactly that format with no embedded commas.

fneily does have it right though ... really bad table design.

 

[elmorro]

GOLOM,
I will try it and post my results.

elmorro

 

[elmorro]

GOLOM,
I tried your suggestion but it did not recognize Left or Mid. Is this an ACCESS query?

elmorro

 

[Golom]

It is an ACCESS query.

What version of Access are you running?

Left and Mid are both standard VBA functions. You may check your project to be sure that you aren't missing some references.