Update Query

#1 (**permalink**) 03-Nov-2005, 11:34 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6489-update-query.html
new field called Street Number and then Delete just the Street number from
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6489
the existing address field.

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

Thanks for any help

*

#2 (**permalink**) 03-Nov-2005, 11:34 AM

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 ?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6489
If it is not consistent every time, provide a couple of examples where there
is not a number and a street name.

"Roger Bell" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6489
> the existing address field.
>
> New fields would appear as: Street Number : 21; Street Name: Smith Street
>
> Thanks for any help
>

#3 (**permalink**) 03-Nov-2005, 11:35 AM

On Wed, 2 Nov 2005 00:00:04 -0800, "Roger Bell"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6489
wrote:

>Is there any way I can design an Update query or other query type to transfer
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6489
>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]

#4 (**permalink**) 03-Nov-2005, 11:35 AM

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.

"Dennis" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6489
> is not a number and a street name.
>
> "Roger Bell" wrote:
>
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6489
> > 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
> >