[MusicalVisions]

I have an MSAccess database that I have created the following query:

SELECT Members.*, [theInput] AS InputVal FROM Members

So when I run this in Access an alert pops requesting 'Enter Parameter Value' requesting a value for theInput . So I type a value in that and everything works splendidly.

Can anyone tell me how to include a parameter in my ASP SQL to pass to Access for the value in InputVal so I don't get a 'No value given for one or more required parameters.' error from the JET Database Engine?

[rokslide]

Instead of executing the query that way why doing you just do the query directly using ado??

[MusicalVisions]

[font=Trebuchet MS]
It is a little more complex than my original post and I was just wondering if there is a trick to do what I asked.

So here goes:

User inputs a zip code on an html form and the system needs to output a recordset of all member record zip codes located within the distance specified.

I plan on using this select query from the page that the form posts to:

q = "SELECT * FROM [Members_ZipSearch] WHERE [Miles]<" & MilesInput & " ORDER BY Miles;"

The database query is currently designed as follows:

I am using the join query ( Members_xZip) from my first post ( SELECT Members.*, [theInput] AS InputVal FROM Members ) to create an extra field to join the UserZip to.

This query is included in Members_ZipSearch, as well as ZIPCodes ( table containing longitude and latitude of US zip codes), and ZIPCodes_1 ( 2nd instance of ZIPCodes)

The query that works fine in access when entering a paramter for user's zip code:

SELECT Members_xZip.*, ZIPCodes.ZIPCode, Members_xZip.zip, ZIPCodes.Latitude, ZIPCodes.Longitude, LatLong_byZip.Latitude_x, LatLong_byZip.Longitude_x, 3963*Atn(Sqr(1-(Sin([latitude]/57.2958)*Sin([latitude_x]/57.2958)+Cos([latitude]/57.2958)*Cos([latitude_x]/57.2958)*Cos([longitude_x]/57.2958-[longitude]/57.2958))^2))/(Sin([latitude]/57.2958)*Sin([latitude_x]/57.2958)+Cos([latitude]/57.2958)*Cos([latitude_x]/57.2958)*Cos([longitude_x]/57.2958-[longitude]/57.2958)) AS miles
FROM (Members_xZip INNER JOIN LatLong_byZip ON Members_xZip.theZip = LatLong_byZip.ZIPCode) INNER JOIN ZIPCodes ON Members_xZip.zip = ZIPCodes.ZIPCode;

Admittedly, there may be a better way to design my query to accomplish this. Any input is appreciated.

Bryan

[MusicalVisions]

Here is the actual query, I had a couple of the field and table names wrong in the previous post.

SELECT Members_xZip.*, ZIPCodes.ZIPCode, Members_xZip.zip, ZIPCodes.Latitude, ZIPCodes.Longitude, LatLong_byZip.Latitude_x, LatLong_byZip.Longitude_x, 3963*Atn(Sqr(1-(Sin([latitude]/57.2958)*Sin([latitude_x]/57.2958)+Cos([latitude]/57.2958)*Cos([latitude_x]/57.2958)*Cos([longitude_x]/57.2958-[longitude]/57.2958))^2))/(Sin([latitude]/57.2958)*Sin([latitude_x]/57.2958)+Cos([latitude]/57.2958)*Cos([latitude_x]/57.2958)*Cos([longitude_x]/57.2958-[longitude]/57.2958)) AS miles
FROM (Members_xZip INNER JOIN ZIPCodes ON Members_xZip.zip = ZIPCodes.ZIPCode) INNER JOIN LatLong_byZip ON Members_xZip.theZip = LatLong_byZip.ZIPCode;

Bryan

[sbl70]

Not sure if you have an answer for your question, but it is rather easy. Just remember to input the paramters in the order they are listed in your query. Also, use quotes for parameters that are strings, dates, etc. You don't have to use quotes for paramters that are numbers.

<%
Dim objRs

Set objRs = objConn.Execute ("exec query_name param1, param2, param3")
%>

You can list as many parameters as you have, just seperate them by commas.

HTH!