Although it can be frustrating at times, digging is often the best way to find things out.... ;)

Anyway, I think you can use the IN keyword to filter multiple boxes, e.g.:

bla bla WHERE Box IN (1, 2, 5)

That would get you three records, for box 1, 2 and 5 (if they exist in the database of course)

Does this help?

Imar

Hi Imar,

This does help - I just have to get the statement to work - as before - if I slect onl;y one value it works - if I select multiples - it returns it in the sql string - but fails to retrieve any records - I think what I need is for it to place an OR after each value - but of course I don't know - I am playing with it now.

Here is the line I am working on: sqlstr= sqlstr + " and Tower IN('" + rssample__vartower + "') "

Any ideas on how to make it work would be appreciated.

Thanks,

Chris

If Tower is a a number in the database, you need to drop the quotes:

Tower IN (1, 2, 3)

not

Tower IN ('1, 2, 3')

Imar

The tower is setup as a text field - if I try to drop the quotes I get a Data type mismatch in criteria expression.


I worked with this in access - it will work if the string would be:

SQL is SELECT * FROM tblsamples WHERE Tower=('01', '02', '03')

I guess I don't know how to get the individual 's around each value?

Chris

Right, in that case, you need to separate each item with a ' like this:

WHERE Tower IN ('1', '2', '3')

This is not as easy to accomplish though.

Two ways to fix it:

1. Change the Tower data type to number in the database. If it's a number, call it a number and not text.

2. In the ASP page, don't use IN (Request.Form) directly, but set up the For Each loop you suggested earlier.

The way I used the IN clause is a quick way to do this. Request.Form("Tower") returns a comma separated list, and the IN clause expects the same. However, Tower being text makes things a little more difficult.

Cheers,

Imar

Hi Imar - I feel busted! I have been feeling like I am posting too much ASP stuff to the dreamweaver forum - so I posted in the asp form section. I guess I am unsure as to where to post (it is dreamweaver but it is also asp)

Anyway - I am making progress on the code - I had tried earlier today to change the databse field to numeric - total disaster - broke every other page I had - restored back to my old code - should be ok now - I think I will use option 2 above.

Chris

Ok Imar,

I am confused again - this worked very well in a different page - but now the best I can get is this:

SQL is SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #01/01/2005# AND #06/28/2005# and Tower='01, 02, 03' and Tower='01, 02, 03' and Tower='01, 02, 03'

the code looks like thus:

sqlstr = "SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #" + rssample__varstorefrom + "# AND #" + rssample__varstoreto + "#"

For Each tower In Request.Form("tower")
rssample__vartower = "*"
If (Request.Form("tower") <> "") Then
rssample__vartower = Request.Form("tower")
If rssample__vartower <> "*" then
sqlstr= sqlstr + " and Tower='" + rssample__vartower + "' "
End if
End If
Next

I will keep at it - but if you see any glaring errors could you let me know.

Chris

No need to feel busted; I was just wondering if this was a different issue.

You're right in that it's probably closer to ASP than to Dreamweaver. However, the Dreamweaver ASP syntax might look a little odd for none DW users, so in the end, both forums might work for you.

Changing a database structure later in the design phase always gives you problems. That's why it's so important to come up with a good design in an early stage. When you changed the type, you broke all pages that assumed the column was text, using single quotes instead of nothing.... Oh well.

You could deploy a little trick in the display of the list items. It's a bit dirty, hard to understand if someone else must change your code later, and it might break other stuff. However, if it's only one page we're talking about here, you might be able to get away with this:

<option value="'1'">1</option>
<option value="'2'">2</option>
<option value="'3'">3</option>

When you get this from Request.Form, it will hold something like:

'1', '2', '3'

which would work in an IN clause:

WHERE Tower IN ('1', '2', '3')

Not pretty though, so it's probably better to use a For loop....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I think you're missing some basics about SQL. Consider this:

Tower ='01, 02, 03'

What does this ask the database? It asks for records that have a Tower column of exactly 01, 02, 03, not for a Tower that has 01 OR 02 OR 03.

In SQL there are a few ways to match records:

=
The = operator does exactly what you expect. It compares a value against a column and returns the record when there is an exact match

LIKE
Compares parts of columns: e.g. LIKE 'some%' would match something, someone etc

IN
IN is a quick way to set up a list filter. E.g. Tower IN (1, 2, 3) returns records that have a Tower of either 1, 2 or 3. Not all three of them, not a mix of them, but only one of those values.

There are more (<>, <, > BETWEEN etc etc) but this should get you in the right direction.

If you have the time and the budget, get your self a copy of Sql Server 2000 Programming. It's targeting Sql Server but you'll find invaluable information about T-Sql in general: http://www.wrox.com/WileyCDA/WroxTit...764543792.html

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks for the reference - I definitely need a book on using SQL - though I am trying to decide where to go - I am currently using access and asp - was trying to decide if I should make the leap to ASP.NET or PHP (I will probably end up using MySQL (because I don't know anyone using MS SQL)) Tough decision - but I figure I can go either way seeing as ASP.NET is very different from ASP 3.0.

I have also solved one of my problems - though it has created a few more for me!!

Here is the solution;

sqlstr = "SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #" + rssample__varstorefrom + "# AND #" + rssample__varstoreto + "#"

rssample__vartower = "*"
If (Request.Form("tower") <> "") Then
rssample__vartower = Request.Form("tower")
If rssample__vartower <> "*" then
dim temp
temp = replace(rssample__vartower, ", ", "','")
sqlstr= sqlstr + " and Tower in ('" + temp + "') "
End if
End If

Now I have broken my ability to have a multiple page repeat region. It is nearly impossible to fix in dreamweaver now - it doesn't see rssample - so the code I had in there from earlier is what I have to tweak. Wish me luck - that code is really ugly!

Chris