Using one user entered field to perform multiple searches

[plantboy99]

I have a field called "zip". The user enters a full 5 digit zip code in the field. My script & SQL statement then accesses the database and shows all the matches for that full zip code. What I want to happen next is perform a search on just the first 3 digits of the zip code to give a regional results list. How do I manipulate my "zip" field to only contain the first 3 digits. I am an RPGIV AS/400 programmer who is new to vbscripting. I do not know of an opcode to let me do this. Thanks for your help!

 

[jfriestman]

Okay, so I am assuming you are accessing your DB thru ASP so here goes some ASP pseudo code that will do what you ask. The gist of it... use a combination of the "LIKE" SQL keyword and "LEFT" VBScript function...<br>
<br>
Note: You didn't specify whether the zip field is numeric or string. I assume it is string for two reasons: 1. There is no reason for it to be numeric as you probably won't be performing mathematical operations on a zip field. And 2. Zip+5 uses a hyphen which requires it to be string.<br>
<br>
<br>
'declare your ADO objects<br>
<br>
SQL = "select * from table where zip = '" & Request("zip") & "'"<br>
<br>
'open the recordset, loop thru the RS and display the results of this first query.<br>
<br>
SQL = "select * from table where zip LIKE '" & Left(Request("zip"),3)% & "'"<br>
<br>
'open the second recordset and do what you like