homepage Welcome to WebmasterWorld Guest from 54.167.144.202
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Select Statement for Access DB
Headed North

5+ Year Member



 
Msg#: 68 posted 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.

 

aspdaddy

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 68 posted 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")

Headed North

5+ Year Member



 
Msg#: 68 posted 4:48 pm on Oct 6, 2005 (gmt 0)

Thanks aspdaddy! That worked perfect.

HN

Headed North

5+ Year Member



 
Msg#: 68 posted 5:16 pm on Oct 6, 2005 (gmt 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

aspdaddy

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 68 posted 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")

Headed North

5+ Year Member



 
Msg#: 68 posted 8:04 pm on Oct 7, 2005 (gmt 0)

Thanks! I'll give that a try.

Really appreciate it.

HN

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved