postal code search

netcrawler · October 19th, 2004, 07:42 AM

Hi i would like to know how to do the following:

I have a search scripts that works nicely to search my postal codes in my database but i want to have it so it searches only the first 2 caracters in the postal code, right now it queries any caracters in the postal code and outputs all the postal codes that has the field that the user enters in the search field.

This is the query part of the search can someone modify it to my needs and show me how I can do what i'm trying to do.

Code:

' Build our query based on the input.
    strSQL = "SELECT *" _
        & "FROM uic " _
        & "WHERE unitpostalcode LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
        '& "OR unitpostalcode LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
        '& "ORDER BY unitpostalcode;"

Thank you very much.

ChrisScott · October 19th, 2004, 10:30 AM

Hi netcrawler,

What is the user entering as search criteria, 2 chars or anything they like?

Cheers,

Chris

netcrawler · October 19th, 2004, 11:42 AM

They will be entering their postal code but I want the search query to do a search only within the first 2 caracters and ignore the rest of the postal code here in Canada we have this standard as postal code: a1a1a1 so what i need to have done is have the query do a search only on the first a1 and disregard the rest of the 4 caracters.

Thanks.

ChrisScott · October 19th, 2004, 11:57 AM

Hey netcrawler,

You should be able to do something like...

Code:

' remove any single quotes (unless needed in Canada)
strSearch = Replace(strSearch, "'", "")

' trim to two chars if required
If Len(strSearch) > 2 Then
    strSearch = Left(strSearch, 2)
End If 

' build query using wild card on the right only so the left chars match left chars in the db
' Build our query based on the input.
    strSQL = "SELECT * FROM uic " & _
        "WHERE unitpostalcode LIKE '" & strSearch & "%' " & _
        "ORDER BY unitpostalcode;"

HTH,

Chris

netcrawler · October 19th, 2004, 12:44 PM

Thats works nice nice work... Thks, ok lets improve the search little bit, here is the scenario:

if 2nd caracter of the postal code is = to 0 then search all 6 caracters---> example A03 K9R then the query would search all 6 caracters.

and

if 2nd caracter of the postal code is between 1-9 search the 2 first caracters ---> example K4K 1K7 then the query would search only the 2 first caracters.

Thanks.

happygv · October 19th, 2004, 01:44 PM

Code:

' trim to two chars if required
If Len(strSearch) > 2 Then
    strSearch = Left(strSearch, 2)
End If

Replace the code given above with the one that follows below.

Code:

If Len(strSearch) > 2 Then
    strSearch1 = Left(strSearch, 2)
End If

If CInt(Right(strSearch1,1))>0 then strSearch = strSearch1

The same strSQL works well for both, so no changes needed in the query suggested by Chris.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection