I'm using Access as my db. I've got a query that I want to pass a parameter to. I use a multiselect dropdown list to get the the users choices to pass. The problem is that I can only figure out how to pass one variable at a time. So let's say the user chooses Albany and Atlanta. I want both of them to be passed to the parameter City.

objComm.Parameters("Required City") = strCity

This is the string I'm using. I've tried the following: "Albany" or "Atlanta", "Albany", "Atlanta" and a bunch of others but none seem to work. Any ideas??

So the actual value of strCity (assuming its request.form()) = "Albany,Atlanta"

Now this is no good as a search string because of the comma.

If the actual search field = "[cityname]" then you could do this :

strCity = replace(strCity, "," , "','")
strCity ="'" & strCity & "'"

This will give

'Albany','Atlanta'

Now, select * from table where city IN ("&strCity &")

OR ->

select * from table where city IN ('Albany','Atlanta')

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart

To make this a little clearer: the first query has a record id and cities. The user can choose from 1 -20 cities. The second query then groups the records by type and creates a total for each type (there are 7 different types).

I can't do this in one query because I'd get total records for each city/type. What I want is simply a total for each type.

I created two SQL statements and then opened two recordsets. I can't seem to use an inner join on the two recordsets.

Any ideas on where to go from here?

code:

---

	Set objConn 	= Server.CreateObject("ADODB.Connection")
	objConn.Open strConn
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open "SELECT COUNT(*) as fldCount FROM Table WHERE Ctiy IN ("&strCity &") AND DataType in ('Option1', 'Option2','Option3','Option3',....) GROUP BY DataType ", objConn
	Do until objRS.EOF
	fldCount = fldCount & CInt(objRS(0)) & ","
	objRS.movenext
	Loop
	objRS.Close
	
	arrCombo = Split(fldCount,",")
	
	Option1 	= Trim(arrCombo(0))
	Option2 	= Trim(arrCombo(1))
	Option3 	= Trim(arrCombo(2))
	Option4 	= Trim(arrCombo(3))
	Option5 	= Trim(arrCombo(4))
	Option6 	= Trim(arrCombo(5)).... etc

---

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart