homepage Welcome to WebmasterWorld Guest from 54.197.147.90
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Select Statement for Access DB
Headed North




msg:1580078
 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




msg:1580079
 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




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

Thanks aspdaddy! That worked perfect.

HN

Headed North




msg:1580081
 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




msg:1580082
 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




msg:1580083
 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