This message was edited by r_patel at 2003-12-18 5:9:57

---

This message was edited by r_patel at 2003-12-18 5:7:47

---

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

This message was edited by rasha at 2003-12-4 3:41:48

---

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

Try this :

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 "

Final sql should look like this:

SELECT TOP 1 *, ABS(CONVERT(INT,zip)- 90000 ) AS 'diff' FROM table
WHERE country = 'USA' ORDER BY diff ASC

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
:
:
:
:

: Try this :
:
:

: 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 "
: 

:
: Final sql should look like this:
:
:

: SELECT TOP 1 *, ABS(CONVERT(INT,zip)- 90000 ) AS 'diff' FROM table
: WHERE country = 'USA' ORDER BY diff ASC
: 

: 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.

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:

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"

The actual query would look like

SELECT TOP 1 zip, country,  Abs(CLng([zip])-07311) AS diff
FROM Table
WHERE country='US'
ORDER BY Abs(CLng(zip)-07311);

:)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
:
: 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..

: This message was edited by r_patel at 2003-12-18 5:7:47

---

: 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
:
:
:
:
:
: