Spliting one field into three

S

Snake

I am trying to split on field into three in a query,

Redgum QLD 4850
Newcastle NSW 2300
Rye VIC 3950
Mount Victoria NSW 2710
Perth WA 6000
Glenelg SA 5100

The number of characters in the first section can vary, in the second
section it will be either two or three characters, in the last section it
will always be three characters.

Any suggestions???

Thanks.
 
S

SteveD

here's one way - I'm sure there are other ways.
I made a make table query (has the original field plus three new fields).
The query then splits the items into the three fields, then an update query
(link the original field on new table to original field of original table)
and update the fields. There must be other ways, but this is just one.

Here's the SQL
SELECT tblTest.Field1, Trim(Mid([field1],1,(Len([field1])-8))) AS F2,
Trim(Mid([field1],Len([f2])+1,4)) AS f3, Right([field1],4) AS f4 INTO
tblTestNew
FROM tblTest;

My original table = tblTest
Original field = Field1
New fields = F2, F3, F4

Hopefully you can follow the logic I used. in words:
F2 = mid(field1, beginning at 1, going over (length of field1-8))
(in other words) all characters from 1 to (8 from the end)
F3 = mid(field1, begin at (length of F2+1), going over 4)
F4 = left(field1, 4 characters)

Note: the Trim function = removes any spaces from the field at both ends

HTH,
SteveD
 
S

Steve Schapel

Snake,

SteveD's suggestion is a good one, as a basic concept. However, given
the variable length of the State field, you will probably need to use
various combinations of InStr([YourField]," ") and
InStrRev([YourField]," ") to get it right.

By the way, Glenelg's postcode is 5045. :)
 
Top