combo box for addresses that ignores door No's on Auto Expand

[efandango]

This could be a bit too ambititious, but...

Is it possible to have a combo box ignore the door number of addresses and
instead match/autoexpand against the first alpha characters it finds? for
example:

If I type 'Spoo' against the list below, the combo will autoexpand on
'Spooner Street' (ignoring the No.20 prefix)

Alpha Street
Beta Street
Ceta Street
Decima Street
10 Sobey Street
Spain Street
Spencer Street
20 Spooner Street
Spout Street
5 North Street

 

[John W. Vinson]

This could be a bit too ambititious, but...

Is it possible to have a combo box ignore the door number of addresses and
instead match/autoexpand against the first alpha characters it finds? for
example:

If I type 'Spoo' against the list below, the combo will autoexpand on
'Spooner Street' (ignoring the No.20 prefix)

Alpha Street
Beta Street
Ceta Street
Decima Street
10 Sobey Street
Spain Street
Spencer Street
20 Spooner Street
Spout Street
5 North Street

Any chance of cleaning up your data?

The problem is that you have a mix of street names and streetnumber street
names. These should be in separate fields. Getting them there will be a
challenge since you might have:

125th St
54 16th Ave

The former is a streetname, the latter contains both.

You can certainly strip numbers out of the field in a calculated value, with a
bit of VBA code, but that will be losing information.

 

[Mike Painter]

This could be a bit too ambititious, but...

Is it possible to have a combo box ignore the door number of
addresses and instead match/autoexpand against the first alpha
characters it finds? for example:

If I type 'Spoo' against the list below, the combo will autoexpand on
'Spooner Street' (ignoring the No.20 prefix)

Alpha Street
Beta Street
Ceta Street
Decima Street
10 Sobey Street
Spain Street
Spencer Street
20 Spooner Street
Spout Street
5 North Street

If it is a really big list, second best is to clean it up yourself as John
says.
First is to buy a normalized address list from the Post Office or some other
source, sometimes mail order houses will process your list for you.
NOTE. "Normalized" is not a relational thing here but what the post office
does to make addresses (uniform.)

An Instr search for the first space in an address will usually get you to
the street name and you can use the mid function to pick it off.
x = Instr(YourAddress, " ")
StreetNumber = Left(Your Address, x)

In here you check to find out if x = 0 and take care of that.
You also make sure that Streetnumber is a number or PO or P.O or a street
without a number.

StreetName =Mid( YourAddress, X + 1)
or maybe not depending on what you got above.

Might as well keep the PO happy and convert to all upper case at the same
time.

 

[BruceM]

If it is a really big list, second best is to clean it up yourself as John
says.
First is to buy a normalized address list from the Post Office or some
other source, sometimes mail order houses will process your list for you.
NOTE. "Normalized" is not a relational thing here but what the post office
does to make addresses (uniform.)

An Instr search for the first space in an address will usually get you to
the street name and you can use the mid function to pick it off.
x = Instr(YourAddress, " ")
StreetNumber = Left(Your Address, x)

In here you check to find out if x = 0 and take care of that.
You also make sure that Streetnumber is a number or PO or P.O or a street
without a number.

StreetName =Mid( YourAddress, X + 1)
or maybe not depending on what you got above.

Might as well keep the PO happy and convert to all upper case at the same
time.

Any attempt to parse the street name will almost surely run into problems.
In the OP's example many streets do not have numbers, so finding the first
space will leave you with "Street". As you mentioned it would be possible
to test for certain values first. For instance:
StreetNameOnly: IIf(Asc(Left([Street],1)) < 65, Right([Street], _
Len([Street] - InStr([Street]," ")), [Street])

Then there would be a problem if the listing is something like "5th Avenue".
Another test could be built whereby if the leftmost character is a number
and the characters to the left of the first space are "st","nd","rd", or
"th" (1st, 2nd, 3rd, 4th, etc.) do not strip off any characters. But there
are likely more exceptions beyond these. I'm not sure about what the OP
would do after buying the list from the PO or whoever, but in terms of the
existing data I doubt there is anything more reliable than John's
suggestion, with which you expressed agreement, to separate the number from
the street name.

 

[Mike Painter]

If it is a really big list, second best is to clean it up yourself
as John says.
First is to buy a normalized address list from the Post Office or
some other source, sometimes mail order houses will process your
list for you. NOTE. "Normalized" is not a relational thing here but
what the post office does to make addresses (uniform.)

An Instr search for the first space in an address will usually get
you to the street name and you can use the mid function to pick it
off. x = Instr(YourAddress, " ")
StreetNumber = Left(Your Address, x)

In here you check to find out if x = 0 and take care of that.
You also make sure that Streetnumber is a number or PO or P.O or a
street without a number.

StreetName =Mid( YourAddress, X + 1)
or maybe not depending on what you got above.

Might as well keep the PO happy and convert to all upper case at the
same time.

Any attempt to parse the street name will almost surely run into
problems. In the OP's example many streets do not have numbers, so
finding the first space will leave you with "Street". As you
mentioned it would be possible to test for certain values first. For
instance: StreetNameOnly: IIf(Asc(Left([Street],1)) < 65, Right([Street],
_
Len([Street] - InStr([Street]," ")),
[Street])
Then there would be a problem if the listing is something like "5th
Avenue". Another test could be built whereby if the leftmost
character is a number and the characters to the left of the first
space are "st","nd","rd", or "th" (1st, 2nd, 3rd, 4th, etc.) do not
strip off any characters. But there are likely more exceptions
beyond these. I'm not sure about what the OP would do after buying
the list from the PO or whoever, but in terms of the existing data I
doubt there is anything more reliable than John's suggestion, with
which you expressed agreement, to separate the number from the street
name.

I just mentioned the "normalized" list because it is not well known. It is
expensive but is the best solution if it will be used a lot. It will pay for
itself if used a lot as it qualifies you fro reduced mailing rates.

 

[BruceM]

efandango wrote:
This could be a bit too ambititious, but...

Is it possible to have a combo box ignore the door number of
addresses and instead match/autoexpand against the first alpha
characters it finds? for example:

If I type 'Spoo' against the list below, the combo will autoexpand
on 'Spooner Street' (ignoring the No.20 prefix)

Alpha Street
Beta Street
Ceta Street
Decima Street
10 Sobey Street
Spain Street
Spencer Street
20 Spooner Street
Spout Street
5 North Street

If it is a really big list, second best is to clean it up yourself
as John says.
First is to buy a normalized address list from the Post Office or
some other source, sometimes mail order houses will process your
list for you. NOTE. "Normalized" is not a relational thing here but
what the post office does to make addresses (uniform.)

An Instr search for the first space in an address will usually get
you to the street name and you can use the mid function to pick it
off. x = Instr(YourAddress, " ")
StreetNumber = Left(Your Address, x)

In here you check to find out if x = 0 and take care of that.
You also make sure that Streetnumber is a number or PO or P.O or a
street without a number.

StreetName =Mid( YourAddress, X + 1)
or maybe not depending on what you got above.

Might as well keep the PO happy and convert to all upper case at the
same time.

Any attempt to parse the street name will almost surely run into
problems. In the OP's example many streets do not have numbers, so
finding the first space will leave you with "Street". As you
mentioned it would be possible to test for certain values first. For
instance: StreetNameOnly: IIf(Asc(Left([Street],1)) < 65,
Right([Street], _
Len([Street] - InStr([Street]," ")),
[Street])
Then there would be a problem if the listing is something like "5th
Avenue". Another test could be built whereby if the leftmost
character is a number and the characters to the left of the first
space are "st","nd","rd", or "th" (1st, 2nd, 3rd, 4th, etc.) do not
strip off any characters. But there are likely more exceptions
beyond these. I'm not sure about what the OP would do after buying
the list from the PO or whoever, but in terms of the existing data I
doubt there is anything more reliable than John's suggestion, with
which you expressed agreement, to separate the number from the street
name.

I just mentioned the "normalized" list because it is not well known. It is
expensive but is the best solution if it will be used a lot. It will pay
for itself if used a lot as it qualifies you fro reduced mailing rates.

Thanks for the information. It's new to me.

 

[efandango]

John,

I can't split the names from the numbers (though it would be quite simple to
do with without access), because this list is kind of unique. It is not a
mailing list (and nevr will be). It is a reference list for a completely
different purpose and because of how it is fed to the database, I need the
kind of solution I was initially suggesting. I was hoping that someone would
be able to come up with an ingenious solution to an unusual problem.

Taking your 125th St and 54 16th Ave eaxamples, I can tell you that firstly
this is a UK list and consequently has a slightly different format. In almost
every instance, the address format will be (where relevant) number first,
then street name. I was hoping for some code that would say something like:

1. Check the first part of the character string for digits and a space.
2. ignore that ### sequence and find on the first example of a alpha
character.
3. expand list of seubsequent characters.

I figured this may involve some deep understanding of how the combo box
actually goes about finding the first character that the user types in and
acting accordingly on that, and that this may well be some microsoft
'internal control' code and not within the scope of Access VBA. For the
record I am not an experienced programmer in almost any sense, and may well
be talking nonsense to a more experienced reader. but as they say, if you
don't ask...

regards

Eric

 

[John W. Vinson]

1. Check the first part of the character string for digits and a space.
2. ignore that ### sequence and find on the first example of a alpha
character.
3. expand list of seubsequent characters.

Well... you can't have it both ways! The autoexpand feature of a combo box
works off of the first displayed column; so it will NOT give you "10 Downing"
if you type a D, and cannot be made to do so.

What you could do (assuming that this meets your needs) is base the combo on a
query with a calculated field:

Streetname: Iif(IsNumeric(Left([Address],1)), Mid([Address] & " ",
InStr([Address] & " ", " ")+1), [Address])

This will trim off any leading number, leaving just the name in the field.

Make this the first visible field in your combo's rowsource, but keep the full
address field in the rowsource for display or storage purposes as appropriate.

 

[efandango]

John,

I realise the Auto-expand does what it is designed to do; and accept that it
can't be altered. But... I think your work-around suggestion may just nail it
for me. the combo does indeed come from a query, albeit a query that is the
tail-end of a convoluted delete and make new table process. But I think if I
work on the end-product query, it may just work.

It's late here now, and I've been on it all day... I will give your
suggestion a try and come back to you.

thank you.

regards

Eric

1. Check the first part of the character string for digits and a space.
2. ignore that ### sequence and find on the first example of a alpha
character.
3. expand list of seubsequent characters.

Well... you can't have it both ways! The autoexpand feature of a combo box
works off of the first displayed column; so it will NOT give you "10 Downing"
if you type a D, and cannot be made to do so.

What you could do (assuming that this meets your needs) is base the combo on a
query with a calculated field:

Streetname: Iif(IsNumeric(Left([Address],1)), Mid([Address] & " ",
InStr([Address] & " ", " ")+1), [Address])

This will trim off any leading number, leaving just the name in the field.

Make this the first visible field in your combo's rowsource, but keep the full
address field in the rowsource for display or storage purposes as appropriate.

 

[BruceM]

You may also need to allow for 125th Street, which I think will be reduced
to "Street". You could check for the presence of a letter in the rightmost
character of what you would strip off the beginning of the string, and strip
it off only if that character is a number.

John,

I realise the Auto-expand does what it is designed to do; and accept that
it
can't be altered. But... I think your work-around suggestion may just nail
it
for me. the combo does indeed come from a query, albeit a query that is
the
tail-end of a convoluted delete and make new table process. But I think if
I
work on the end-product query, it may just work.

It's late here now, and I've been on it all day... I will give your
suggestion a try and come back to you.

thank you.

regards

Eric

1. Check the first part of the character string for digits and a space.
2. ignore that ### sequence and find on the first example of a alpha
character.
3. expand list of seubsequent characters.

Well... you can't have it both ways! The autoexpand feature of a combo
box
works off of the first displayed column; so it will NOT give you "10
Downing"
if you type a D, and cannot be made to do so.

What you could do (assuming that this meets your needs) is base the combo
on a
query with a calculated field:

Streetname: Iif(IsNumeric(Left([Address],1)), Mid([Address] & " ",
InStr([Address] & " ", " ")+1), [Address])

This will trim off any leading number, leaving just the name in the
field.

Make this the first visible field in your combo's rowsource, but keep the
full
address field in the rowsource for display or storage purposes as
appropriate.

 

[efandango]

John,

Just to let you know that your suggestion worked really well. I had to
change an IDfield from Number to text on the lookup address combo, which
forced similar changes to various tables and queries; but once all the
changes were made, it all came together. One small issue though; whereever I
have a multi-door/street number like:

'20-23 Any Street', the combo presents it as '-23 Any Street' is their a way
of dealing with hypthenated numbers like that?

regards

Eric

John,

I realise the Auto-expand does what it is designed to do; and accept that it
can't be altered. But... I think your work-around suggestion may just nail it
for me. the combo does indeed come from a query, albeit a query that is the
tail-end of a convoluted delete and make new table process. But I think if I
work on the end-product query, it may just work.

It's late here now, and I've been on it all day... I will give your
suggestion a try and come back to you.

thank you.

regards

Eric

1. Check the first part of the character string for digits and a space.
2. ignore that ### sequence and find on the first example of a alpha
character.
3. expand list of seubsequent characters.

Well... you can't have it both ways! The autoexpand feature of a combo box
works off of the first displayed column; so it will NOT give you "10 Downing"
if you type a D, and cannot be made to do so.

What you could do (assuming that this meets your needs) is base the combo on a
query with a calculated field:

Streetname: Iif(IsNumeric(Left([Address],1)), Mid([Address] & " ",
InStr([Address] & " ", " ")+1), [Address])

This will trim off any leading number, leaving just the name in the field.

Make this the first visible field in your combo's rowsource, but keep the full
address field in the rowsource for display or storage purposes as appropriate.

 

[John W. Vinson]

John,

Just to let you know that your suggestion worked really well. I had to
change an IDfield from Number to text on the lookup address combo, which
forced similar changes to various tables and queries; but once all the
changes were made, it all came together. One small issue though; whereever I
have a multi-door/street number like:

'20-23 Any Street', the combo presents it as '-23 Any Street' is their a way
of dealing with hypthenated numbers like that?

Very odd. It should be parsing of "20-23 " by finding the first blank, rather
than treating the hyphen as a blank. I don't have time to experiment with it
this weekend but I'll leave this thread open to remind me and try to look at
it Monday.