Update Query

[Guest]

Is there any way I can design an Update query or other query type to transfer
street numbers which form part af an address field: EG: 21 Smith Street to a
new field called Street Number and then Delete just the Street number from
the existing address field.

New fields would appear as: Street Number : 21; Street Name: Smith Street

Thanks for any help

 

[Guest]

How consistent is your data ?
Does every address field start with a street number and then a space to the
street name ?
Is the number and name in the same field every time ?
If it is not consistent every time, provide a couple of examples where there
is not a number and a street name.

 

[John Vinson]

Is there any way I can design an Update query or other query type to transfer
street numbers which form part af an address field: EG: 21 Smith Street to a
new field called Street Number and then Delete just the Street number from
the existing address field.

New fields would appear as: Street Number : 21; Street Name: Smith Street

Thanks for any help

An answer... and a warning.

You can do this with an Update query by adding *two* new fields to
your table - StreetNumber and StreetName. Update StreetNumber to

Left([Address], InStr([Address], " ") - 1)

and StreetName to

Mid([Address], InStr([Address], " ") + 1)

The warning: I've seen addresses like

312 1/2 Hickory Lane => Street becomes "1/2 Hickory Lane"
225 A Maple St. => Street becomes "A Maple Street"
Ballacraine => Generates an error

John W. Vinson[MVP]

 

[Guest]

Thank you Dennis,
Yes, in the main, the address field does start with a street number and a
space to the street name. On the very odd occasion the street number is
missing and just shows the street name EG:Lot 14 Smith Street, however, the
street number field is currently blank in all cases.

Any further advice would be appreciated, as I inherited the data base this
way and would like to split the address as described above.