splitting a field

[Jean-Paul]

Hi,
Currently I have a field called "Street"
It contains data like: Spoorweglaan 7 or Grote Baan 125 bus 15
I created an extra field called "Number"
What I would like is to have a query that can split my initial field
into 2 fields so I finally will get:

Street: Spoorweglaan Number: 7
Street: Grote Baan Number: 125 bus 15

Can this be done and how?
Thanks

 

[Jeff Boyce]

Jean-Paul

Are you saying that the instructions you'd give an assistant are to start at
the left, continue until you locate the first digit, then call everything to
the left the "street" and everything to the right, the "street number"
(NOTE: "Number" is a reserved word in Access, don't use it as a name for a
field)?

Are you quite confident that none of your data is like: Grote Baan Seven
bus 15?

Take a look at the Left(), Right() and Instr() functions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TedMi]

Besides Jeff's warning about spelled-put numbers, you also need to take into
account numeric street names, e.g. 23 Str. 5 bus 15
-TedMi