Concatenate part of two textboxes and then save the result

[Popeye]

Hi everyone

A user enters an address (address1, address2, address3, city, postcode) and
I need to generate a unique reference for the property. In order to do this,
I'd like to concatenate the postcode and the house number. The resultant
reference should be stored in the table in a field called <Reference>.

I can have a (hidden) textbox on the form and it's control source set to
"=[postcode] & ", " & [address1]", but this takes the whole of the first line
of the address. How do I strip out the house number? Sometimes the user
separates the house number from the street name with a space and sometimes
with a comma.

I assume that all spaces should be stripped out of address1 and then the
string stepped until a non-numeric is reached. That's where my knowledge
ends so the questions are:

How do I strip out the house number and how do I ensure that the
concatenated property reference is stored in the Reference field in the table?

Thanks for your patience.

 

[Allen Browne]

If the street number is the first numeric characters of the Address1 field,
you could parse it with:
Val([Address1])

Of course, that won't work with addresses such as:
Unit 7 / 555 Main St
7 / 555 Main St
Lot 7 Main Rd
First floor, 555 Main St
TNG Buiding, 555 Main St
Cnr Main St and Cross Rd
P O Box 44
and so on.

It also seems to me that the street number and postal code will not be
unique, since a house number could appear in many streets in the same
postcode area.

To answer your question, you could use the AfterUpdate event procedure of
both Address1 and Postcode to write the value of the Reference field:
If Not (IsNull(Me.Postcode) Or IsNull(Me.Address1)) Then
Me.Reference = Me.Postcode & ", " & Val(Me.Address1)
End If

 

[Popeye]

Hi Allen

Yes, I realise that there could be problems with an address which doesn't
start with a number, or has a letter as part of the number (15a High Street
or 23c, Joseph Avenue), that's why I wondered if there was an easy way of
stripping off what comes before the first space or comma.

As a matter of interest, the postcodes in the UK are fairly well localised
to individual roads or, in a long road, certain buildings in it. The format
isn't very friendly and I'm sure you've seen threads about the difficulty
creating an input mask for UK postcodes. The system in the US is more
logical but I assume that a particular zip code covers a much wider area than
in the UK. I don't know the system in Australia. If we, in the UK purchase
something over the telephone, we are often simply asked for our postcode and
house number and the vendor checks their database to locate the exact address.

Thank you for your suggestions. I'll also look further into the possibility
of using code to cycle through the characters in Address1 (also in the
AfterUpdate events that you mentioned) and use those characters which occur
before <space> or <comma>

 

[Allen Browne]

Instr() will locate a character in a string if that's the way you want to
go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, I realise that there could be problems with an address which doesn't
start with a number, or has a letter as part of the number (15a High
Street
or 23c, Joseph Avenue), that's why I wondered if there was an easy way of
stripping off what comes before the first space or comma.

As a matter of interest, the postcodes in the UK are fairly well localised
to individual roads or, in a long road, certain buildings in it. The
format
isn't very friendly and I'm sure you've seen threads about the difficulty
creating an input mask for UK postcodes. The system in the US is more
logical but I assume that a particular zip code covers a much wider area
than
in the UK. I don't know the system in Australia. If we, in the UK
purchase
something over the telephone, we are often simply asked for our postcode
and
house number and the vendor checks their database to locate the exact
address.

Thank you for your suggestions. I'll also look further into the
possibility
of using code to cycle through the characters in Address1 (also in the
AfterUpdate events that you mentioned) and use those characters which
occur
before <space> or <comma>

If the street number is the first numeric characters of the Address1
field,
you could parse it with:
Val([Address1])

Of course, that won't work with addresses such as:
Unit 7 / 555 Main St
7 / 555 Main St
Lot 7 Main Rd
First floor, 555 Main St
TNG Buiding, 555 Main St
Cnr Main St and Cross Rd
P O Box 44
and so on.

It also seems to me that the street number and postal code will not be
unique, since a house number could appear in many streets in the same
postcode area.

To answer your question, you could use the AfterUpdate event procedure of
both Address1 and Postcode to write the value of the Reference field:
If Not (IsNull(Me.Postcode) Or IsNull(Me.Address1)) Then
Me.Reference = Me.Postcode & ", " & Val(Me.Address1)
End If

 

[Popeye]

Thank you Allen.

 

[Homer J Simpson]

Yes, I realise that there could be problems with an address which doesn't
start with a number, or has a letter as part of the number (15a High
Street
or 23c, Joseph Avenue), that's why I wondered if there was an easy way of
stripping off what comes before the first space or comma.

It's better to work backwards. Figure out what you need, then make your
input form force the user to enter sane data that is validated at the point
of entry. If they CAN enter junk, they WILL enter junk.