split up adres no.

[Keesb]

I have a database with a field with an adress number in it. Some of these
numbers also have an "non number" in it. f.e. 45a or 45-a.

I like to spilt this up into 2 fields so 45a must be split up in one field
the "45" and in an other field the "a"
Is their some function to do so?

regards

Kees

 

[Al Campagna]

Keesb,
If your address "numbers" (really text) were absolutely consistant (ex. ALL were of the
format "XX-A"), you could locate the "-" in each address string using the InStr function,
and through string manipulation, split the AddrNum from the AddrLetter and save into 2 new
fields in your table.

Given "44-A"
Left(AddrNo, InStr(AddrNo, "-") - 1)) 'would yield the number 44
Mid(AddrNo, InStr(AddrNo, "-") +1)) 'would yield the letter/s "A"
This will not work for "44A"...

However, in my experience, I have never seen a database designed that way.

Why would you need to determine the number portion of say "44-A". You won't be
operating on that as a number... (ex Add, Avg, Max, etc..) nor would sorting by an AddrNo
yield any useful information. Sorting 44 Main St with 44 Spring St is not a logical way
to view or analyze your data.

The Address field should be one string field, like "44-A South Main St."

 

[Keesb]

Hi Al Campagna,

Thanks for your replay. This data will be used for export and import into
another applicatie. In that applications it must be splits into 2 fields. So
thats the reason.

And your right, the "-" is not always used is can also be 44a or 44 a.
Is it not possible to look just for numbers? Than I can splits the data with
a number in it, into 1 field and the data without a number into another
field?

Kees