Query Issue

[rparish]

I keep getting this error:

error '80004005'
/view_expense_report2x.asp, line 240


"SELECT SUM(cost) AS [RecordSum] FROM expensereport WHERE mydate BETWEEN #" & firstDate & "# AND #" & lastDate & "# and team='teamname' AND names='"&Request.QueryString("names")&"';"


There is no problem when I do this, but I need to have it only include what querys from the names field

"SELECT SUM(cost) AS [RecordSum] FROM expensereport WHERE mydate BETWEEN #" & firstDate & "# AND #" & lastDate & "# and team='teamname'"


Please let me know where I am going wrong.

[nikkiH]

Your string values must be quoted as you have it set up to do, but the = sign only works for one value, not multiple.

You need an IN instead, and you need a little function to wrap the strings in quotes.
Something like this. (look up replace syntax, you want to replace commas with quote comma quote)

AND names in ('" & Replace(Request.QueryString("names"),",","','") & "')"

I'm assuming names has values that are comma-separated.

Do be careful with the possibility of the names having apostrophes in them. That kills straight sql like this. You may want to do an additional replace of a single quote with two single quotes or whatever your database uses as an escape character.

And not checking the query string for sql injection attacks is begging for trouble. ;)

[rparish]

names is actually a number field. I did not know you can only have 1 = sign in a query.

I am going to look into an in statment.

[nikkiH]

Oh, that's easier then :)
No quotes at all needed.

AND names in (" & Request.QueryString("names") & ")"

Still beware sql injection attacks; this is a classic opening.