Welcome to WebmasterWorld Guest from 54.198.118.102

Forum Moderators: open

Message Too Old, No Replies

Select Statement for Access DB

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

New User

10+ Year Member

joined:Oct 6, 2005
posts:33
votes: 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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
posts:2162
votes: 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)

New User

10+ Year Member

joined:Oct 6, 2005
posts:33
votes: 0


Thanks aspdaddy! That worked perfect.

HN

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

New User

10+ Year Member

joined:Oct 6, 2005
posts:33
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
posts:2162
votes: 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)

New User

10+ Year Member

joined:Oct 6, 2005
posts:33
votes: 0


Thanks! I'll give that a try.

Really appreciate it.

HN

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members