Split columns in 2

[n2nuk]

Hi there,

I have a table of client addresses. I have to produce a dataset based on this table to import into another DB system. The problem I have is that my table records the clients address as '23 anywhere road' as an example. The target DB has seperate columns for house number and street name.
The trouble also is that our DB supports separate columns for house number and street, in some cases these are coded correctly.

Any idea's how I go about creating these columns in a query?

Thanks

Naz

 

[DotNetGnat]

since the user inputs may vary significantly...how can you make sure which is house number and which is street address...

if the user enter 23 anywhere, then i can get the 23 as house number and anywhere as street using the split function...

but what if the user enters "apt23 A/100 anywhere" now which one is the house number??

i would suggest you to provide two different input boxes to the user to enter house number and street address separately...

-DNG

 

[n2nuk]

Thanks for the quick response,

Apologies for being a bit vague, the flat number actually recrods the room number not the house number. Hence the house number field.

Hopes this makes sense.

Thanks again

Naz

 

[dhookom]

naziern,
The question is "do all of your records have a number followed by a space followed by the street name?" Or, do you have some other patterns of values in the field that are predictable?

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]

 

[n2nuk]

Hi dhookom,

The answer to your query would be a big YES!

Thanks

Naz

 

[dhookom]

You should be able to use some of the string functions such as:
HouseNum: Left([address], Instr([Address]," ")-1)
Street: Mid([address], Instr([Address]," ")+1)


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]