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

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.