|Select Statement for Access DB|
| 1:50 pm on Oct 6, 2005 (gmt 0)|
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)|
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)|
Thanks aspdaddy! That worked perfect.
| 5:16 pm on Oct 6, 2005 (gmt 0)|
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?
| 7:48 pm on Oct 7, 2005 (gmt 0)|
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)|
Thanks! I'll give that a try.
Really appreciate it.