Query customer address using "between"

[jmacnwd]

I am trying to query a range of customer address' from a text field in a table. ex: address' between "200 college" and "800 college". How do I enter it so that it doesn't give the results of everything between 200 & 800 without regard to the street name?

 

[dcx693]

Use something like this for the criteria for the address field:
Between "200 College" And "800 College"

 

[jmacnwd]

Use something like this for the criteria for the address field:
Between "200 College" And "800 College"

When I do that it queries the address numbers between 200 and 800 but the address name is not specific to "College", it is all street names!

 

[dcx693]

Right you are. Durn it! I'm guessing that you'll have to separate the numeric portion out from the rest of the address. You can use the Val function to get the numeric portion of the street address (I'm assuming the number portion always starts at the beginning of the string). If the Val function returns a number > 0, then use the Instr() function to get the rest of the string. Have the query only return those addresses with the same street, then you can use the Between criteria with the result from the Val function to find if the street number is between your end points.

 

[jmacnwd]

Right you are. Durn it! I'm guessing that you'll have to separate the numeric portion out from the rest of the address. You can use the Val function to get the numeric portion of the street address (I'm assuming the number portion always starts at the beginning of the string). If the Val function returns a number > 0, then use the Instr() function to get the rest of the string. Have the query only return those addresses with the same street, then you can use the Between criteria with the result from the Val function to find if the street number is between your end points.

Ok, PLEASE help me w/a creative way to do this! The person that will be using this DB is limited w/experience. I created a pop-up form for them to enter the param criteria in the query. Also, if the info is entered in multiple fields on the Data Entry form, it will get confusing entering the data; ex. 200 BLK E 5TH BETWEEN 600 BLK COLLEGE & MARIS. Thanks for any help that you can provide! Kevin

 

[dcx693]

OK, well I don't have much input for the more complex cases, but if your addresses are going to be basically a number and a street name, then what you can do is use a function to place the street name before the number, so that street addresses will all group together correctly. What it will do is take an address like "300 College" and change it to "College 300". This will all happen in the background, so the user won't need to know what's going on.

If you've never used a custom function before, don't worry, it's not too crazy. Below is the text. Copy and paste it into a new blank module and save it. You can pretty much call the saved module anything you want except RevAddress (it will confuse Access if you name the function and the module the same name):

Function RevAddress(strAddress As String) As String
Dim intNumber As Integer
Dim strStreet As String

    intNumber = Val(strAddress)
    If intNumber = 0 Then
        strStreet = strAddress
    Else
        strStreet = Mid(strAddress, InStr(1, strAddress, " ") + 1)
    End If
    
    RevAddress = strStreet & " " & intNumber
    
End Function

It first checks to see if the address begins with a number. If it doesn't, then addresses like "St. John's Hospital" stay that way. (I don't know how often this happens, but I think I've seen it before.)

Where will you use the function? In a query. If you want to use a parameter query (where the query pops up a dialog box for the user to enter the start and ending addresses), create a query and add the table containing your addresses to it. Then add the field with the street address to the QBE grid. I'll assume your field is called MyAddress. Use this as the criteria:

RevAddress([MyAddress]) Between RevAddress([Please enter the starting address]) And RevAddress([Please enter the ending address])

 

[Pat Hartman]

If your application requires this type of processing, it is important to separate out the various parts of a street address and the best place to do that is at data entry time. Converting existing data is going to be a real problem due to the variablilty of address formats.

 

[jmacnwd]

Thanks for all who replyed! I will have to think on this one!