Nearest Zip code - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Nearest Zip code

[b][red]This message was edited by r_patel at 2003-12-18 5:9:57[/red][/b][hr]
[b][red]This message was edited by r_patel at 2003-12-18 5:7:47[/red][/b][hr]
Hi,

I am trying to retrieve records from my Access database.
I want to retrieve those records whose zip = zipcode (from a form)
or if that perticular zipcode is not present than find for a nearest zip code to display the record.

select * from table where country = '" + cstr(trim(Request("country"))) + "' and zip='" + cstr(trim(Request("zipcode"))) + "' or zip= "nearest zipcode query"

How to write a query on such selection

Can anybody help me out.

r_patel







Comments

  • rasharasha Posts: 25Member
    [b][red]This message was edited by rasha at 2003-12-4 3:41:48[/red][/b][hr]
    Hi,
    I do not know if what I will say will help or not but try
    You can use the wildcard operators to get the nearest zipcode.
    your statement will be
    select * from table where country = '" + cstr(trim(Request("country"))) + "' and zip='" + cstr(trim(Request("zipcode"))) + "' or ziplike 'Br%'

    Examples:

    Like 'Br%' This will retrieve every zipcode begining with the letters Br
    Like 'BR%' This will retrieve every zipcode begining with the letters BR
    Like '%een' This will retrieve every zipcode ending with the letters een
    Like '%en%' This will retrieve every zipcode containing the letters en
    Like '_en' This will retrieve every 3 letter zipcode ending in the letters en
    Like '[ck]%' This will retrieve every zipcode beging with the letters c or k
    Like '[s-v]ing' This will retrieve every zipcode ending in letters ing and begining with any single letter from s to v
    Like 'M[^c]%' This will retrieve every zipcode begining with the letters M that does not have the letter c as the secvond letter

    see the above are examples, you may adjust it to suit your needs

    Rasha, Egypt


  • vanithvanith Posts: 44Member
    Try this :

    [code]
    sql = " SELECT TOP 1 *, ABS(CONVERT(INT,zip) - "
    sql = sql & CLng(trim(Request("zipcode"))) + " ) AS 'diff' "
    sql = sql & " FROM table "
    sql = sql & " WHERE country = '" & cstr(trim(Request("country"))) & "' "
    sql = sql & " ORDER BY diff ASC "
    [/code]

    Final sql should look like this:

    [code]
    SELECT TOP 1 *, ABS(CONVERT(INT,zip)- 90000 ) AS 'diff' FROM table
    WHERE country = 'USA' ORDER BY diff ASC
    [/code]
    assuming Request("country") = "USA" and Request("zip") = "90000"


    :)heers,
    Vanith




    : Hi,
    :
    : I am trying to retrieve records from my Access database.
    : I want to retrieve those records whose zip = zipcode (from a form)
    : or if that perticular zipcode is not present than find for a nearest zip code to display the record.
    :
    : select * from table where country = '" + cstr(trim(Request("country"))) + "' and zip='" + cstr(trim(Request("zipcode"))) + "' or zip= "nearest zipcode query"
    :
    : Can anybody help me out.
    :
    : r_patel
    :
    :
    :
    :

  • r_patelr_patel Posts: 29Member
    : Try this :
    :
    : [code]
    : sql = " SELECT TOP 1 *, ABS(CONVERT(INT,zip) - "
    : sql = sql & CLng(trim(Request("zipcode"))) + " ) AS 'diff' "
    : sql = sql & " FROM table "
    : sql = sql & " WHERE country = '" & cstr(trim(Request("country"))) & "' "
    : sql = sql & " ORDER BY diff ASC "
    : [/code]
    :
    : Final sql should look like this:
    :
    : [code]
    : SELECT TOP 1 *, ABS(CONVERT(INT,zip)- 90000 ) AS 'diff' FROM table
    : WHERE country = 'USA' ORDER BY diff ASC
    : [/code]
    : assuming Request("country") = "USA" and Request("zip") = "90000"
    :
    :
    : :)heers,
    : Vanith
    :


    Hi Vanith,

    I tried the same way, but it says 'undefined function "CONVERT" in expression'. And one more thing that, I have set the datatype for zip as Text in my table, should I change it to some othere data type?

    Please help me ASAP.


  • vanithvanith Posts: 44Member
    Ooops! My mistake.. I didn't notice that you are using an Access database. that would work good in SQL server. Here's the equivalent SQL script for Access.

    asp code:
    [code]
    sql = " SELECT TOP 1 *, ABS(CLng(zip) - "
    sql = sql & CLng(trim(Request("zipcode"))) + " ) AS diff "
    sql = sql & " FROM table "
    sql = sql & " WHERE country = '" & cstr(trim(Request("country"))) & "' "
    sql = sql & " ORDER BY "
    sql = sql & " ABS(CLng(zip) - " & CLng(trim(Request("zipcode"))) & " ) ASC"
    [/code]


    The actual query would look like
    [code]
    SELECT TOP 1 zip, country, Abs(CLng([zip])-07311) AS diff
    FROM Table
    WHERE country='US'
    ORDER BY Abs(CLng(zip)-07311);
    [/code]

    :)heers,
    Vanith
  • RupeshNRupeshN Posts: 8Member
    : Hi,
    :
    : I am trying to retrieve records from my Access database.
    : I want to retrieve those records whose zip = zipcode (from a form)
    : or if that perticular zipcode is not present than find for a nearest zip code to display the record.
    :
    : select * from table where country = '" + cstr(trim(Request("country"))) + "' and zip='" + cstr(trim(Request("zipcode"))) + "' or zip= "nearest zipcode query"
    :
    : Can anybody help me out.
    :
    : r_patel
    :
    : Your would just like to display records like search method right for
    : that i would like to suggest something.

    : select * from table where country = '" + cstr(trim(Request("country"))) + "' and zip like '%" + (trim(Request("zipcode")) + "%'"

    : I think the zipcode would be u would be storing the entered value..
    : I hope this would work for u..



  • r_patelr_patel Posts: 29Member
    : [b][red]This message was edited by r_patel at 2003-12-18 5:7:47[/red][/b][hr]
    : Hi,
    :
    : I am trying to retrieve records from my Access database.
    : I want to retrieve those records whose zip = zipcode (from a form)
    : or if that perticular zipcode is not present than find for a nearest zip code to display the record.
    :
    : select * from table where country = '" + cstr(trim(Request("country"))) + "' and zip='" + cstr(trim(Request("zipcode"))) + "' or zip= "nearest zipcode query"
    :
    : Here nearest zip code means, if I enter 12234, & this zipcode is not present then, I want to search 12235 else 12236 etc.. till it gets something like 12241...
    :
    : How to write a query on such selection
    :
    : Can anybody help me out.
    :
    : r_patel
    :
    :
    :
    :
    :
    :

Sign In or Register to comment.