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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Popeye" <(E-Mail Removed)> wrote in message
news00D3DAA-0DF6-4E9F-A356-(E-Mail Removed)...
> 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.
>

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" wrote:

> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Popeye" <(E-Mail Removed)> wrote in message
news:C327451E-D2F6-4C76-90CD-(E-Mail Removed)...
>
> 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" wrote:
>
>> 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

Thank you Allen.

"Allen Browne" wrote:

> Instr() will locate a character in a string if that's the way you want to
> go.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>

"Popeye" <(E-Mail Removed)> wrote in message
news:C327451E-D2F6-4C76-90CD-(E-Mail Removed)...

> 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.