Welcome to WebmasterWorld Guest from 23.23.53.177

Forum Moderators: open

Message Too Old, No Replies

Select Statement for Access DB

   
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.

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")
4:48 pm on Oct 6, 2005 (gmt 0)

5+ Year Member



Thanks aspdaddy! That worked perfect.

HN

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

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")

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

5+ Year Member



Thanks! I'll give that a try.

Really appreciate it.

HN

 

Featured Threads

Hot Threads This Week

Hot Threads This Month