Can combo box me made to ignore the prefix street number on addres

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an ‘Auto Expand’ combobox for addresses. The users know most of the
addresses; and in an ideal situaton would just type the first few characters
until the correct address appeared in the box. Except it’s not an ideal
situation because the addresses very often have a street number prefix like
the examples below:


39 Abbot St, N1
Abbotsford St, N1
22 Abby Rd, N4
113 Aberdeen St, N4
99 Acacia Rd, N1
Academy Ct, N1
Academy Rd, N1
Academy Ter, N1
17 Academy Hill Rd, N1
Acadia St, N1
Accolon Way,N1

Is there a way that the combo box could ignore the prefix numbers, so that
the user can just type Abb… for: 39 Abbot St?
 
This type of senerio is the very reason that I have started spliting the
address from the street. I used to use just a "Address" field. I now create
two fields. One to hold the address, ie. the numbers, and another field to
hold the street.

Once the address is seperated from the street, there are many more options
for users to find what they are looking for. For example, I can display all
addresses in a list box, with both the address and the street concatenate
together. Then I might offer the user an option group where a selection for
how to find the desired address would be selected, but street or by address.
If they select "by street", I would show them a combo box loaded with unique
street names from the database. When a selection is made form the combo box,
I would use code to filter the listbox with only those records with one
recrods for the selected street. If they select "by Address", I would
present a textbox where they can type in the address and then use code to
find the first occurrance of the value typed and then also provide a "Find
Next" option to allow the user to eventually locate the desired value.

With all of this said, to answer your question, I know of no way to have
Access just "ignore the prefix numbers". You might be able to use some sort
of find or filter method involving the use of an "*" in the criteria to
locate records with the value provided by the user.

Someone else may have a better idea.

Good luck.
 
Thanks for the response MR B,

As I said, its not an ideal world, and unfortunatey I cannot do this with my
address data, as it is being constantly brought in to the database from other
sources. But Its something I'll bear in mind if i have control over the
origin of the data.

to be honest, I thought it was a long shot to ask for this kind of thing,
but nothing ventured...

regards
 
efandango,

I believe that it is impossible for you to have the Stree field.

If the format of your data is as consistant as the examples you posted, then
you could creat a "Street" field from the existing data by using a custom
function and use that function in an update query after the data is imported
to update a new field for the Stree only.

Here is a function that I tested against the examples you posted and in
every case it returned only the street, leaving off the address. This would
allow you to have a street field by using this function is an update query.

'*****Start of Code*****
Public Function GetStreetOnly(CurAddress)
Dim bytChrLoc As Byte
Dim strPrevChar As String

'check for a space in the address
bytChrLoc = InStr(1, CurAddress, " ")
'if there is a space the "bytChrLoc" variable
'holdss the loction of the first space
If bytChrLoc > 0 Then
'use the location of the space to check the
'first character to the left of the space
strPrevChar = Mid(CurAddress, bytChrLoc - 1, 1)
If IsNumeric(strPrevChar) Then
'if this character is numeric then is it
'assumed that this is the Address
GetStreetOnly = Right(CurAddress, Len(CurAddress) - bytChrLoc)
Else
'if this character is not numeric then
'it is part of the Street
GetStreetOnly = CurAddress
End If
Else
'if there is no space in the address then
'the street is all that is there
GetStreetOnly = CurAddress
End If
End Function
'*****End Of Code*****

The function can be called by simply passing in the entire address.
 
There's no easy way to do exactly what you want. You can set the
RowSource of the combo box to a query that returns the street names
without the numbers and thereby get the AutoExpand working on the names
without the numbers - but the numbers won't be displayed. I.e. the list
would be

Abbot St, N1
Abbotsfort St, N1
Abby Rd, N4
...

Would that be OK? If it's important to display the street numbers you
could get the query to display them in a separate column

Abbot St, N1 39
Abbotsfort St, N1
Abby Rd, N4 22
...

or append them to the street names

Abbot St, N1 (39)
Abbotsfort St, N1
Abby Rd, N4 (22)
...

The combo box would need a zero-width (i.e. hidden) first column
containing the primary key of the Addresses table. This is what I've
just been using in my test database:

SELECT ID, rgxReplace(Address, "^\d\w*\s", "") AS ModAddress FROM
AddrNew;

rgxReplace() is a function on my website at
http://www.j.nurick.dial.pipex.com/Code/index.htm. It's possible to use
standard VBA functions to dispose of the street number; I just used
rgxReplace and a regular expression to save thinking<g>.

Hope this helps: post back if it doesn't make sense.
 
Hello John,

Thanks for the response; it's encouraging to think it can be done. I'm
somewhat of a Newbie with MS Access. I will digest your feedback, get my head
around it, and see if i can incorporate it, into my fairly complicated
database, that is looking more like a full blown program by the day; i'm sure
you have been there..., constantly pushing the bounderies of functionality. I
have things working more/less how I want them, and need to sit back and
evaluate the next move (Its been a lot of late nights). I really appreciate
your feedback, and in time will come back to you to let you know how i got on.

regards

Eric
 
Back
Top