Welcome to WebmasterWorld Guest from 54.196.224.166

Forum Moderators: open

Message Too Old, No Replies

Select Statement for Access DB

     

Headed North

1:50 pm on Oct 6, 2005 (gmt 0)

5+ Year Member



I have a form set up that allows users to check up to 4 check boxes and the system will then search the database for the correct records to be returned. The checkboxes are all named PERFORMER_LEVEL and the values for the checkboxes are 1, 2, 3, 4. So, if they check more than 1 checkbox, the value sent to the search is something like 1,2,3. I need a SQL statement that selects records from the schedule table where the PERFORMER_LEVEL field value is contained in the field.

So, for example, the user checks 2 checkboxes and the value sent to the search page is 1,2. The SQL statement needs to select each record in the schedule table that contains either a 1 or 2 in the PERFORMER_LEVEL field. The values in the PERFORMER_LEVEL field of the db are all just 1 number. Ex. 1

The start of my SQL statement looks like this:
Set RS = DB.execute("SELECT * FROM schedule WHERE PERFORMER_LEVEL=" ........... "ORDER BY event_date, start_time ASC")

Can anyone help me complete this statement?

Thank you in advance.

aspdaddy

3:21 pm on Oct 6, 2005 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Set RS = DB.execute("SELECT * FROM schedule WHERE PERFORMER_LEVEL IN (" & Request("PERFORMER_LEVEL") &") ORDER BY event_date, start_time ASC")

Headed North

4:48 pm on Oct 6, 2005 (gmt 0)

5+ Year Member



Thanks aspdaddy! That worked perfect.

HN

Headed North

5:16 pm on Oct 6, 2005 (gmt 0)

5+ Year Member



aspdaddy,

would that select statement need to be altered if the values were not numbers but were in fact text strings set as text datatype in the Access DB?

Thanks,
HN

aspdaddy

7:48 pm on Oct 7, 2005 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Yeah each value would need to be enclosed in single quotes, you can do it with a replace like this:

Set RS = DB.execute("SELECT * FROM schedule WHERE PERFORMER_LEVEL IN (" & "'" & Replace(Request("PERFORMER_LEVEL"),",","','") & "'" &") ORDER BY event_date, start_time ASC")

Headed North

8:04 pm on Oct 7, 2005 (gmt 0)

5+ Year Member



Thanks! I'll give that a try.

Really appreciate it.

HN