Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-16 : 22:25:35
|
Andy writes "I have a form with 5 drop down boxes for a user to choose different criteria to build a form. The user can choose them in any variety of ways (i.e. just select 1, 2, or all 5). I have set up my sql by setting the beginning part to strSql_Begin. This part houses my SELECT, and WHERE clauses. The second part is my AND clauses. This is what is troubling me. I need to have an AND statemnt for each drop down box that is selected on the form. Currently I am using If statements for every possible option and setting that to a variable called strAnd. I would like to know if there is a muhc simpler way to do this...preferably just five differnt statements. I have attached some code below to show how I am buliding the AND statements at the present time. Any ideas to simplify this will be greatly appreciated. Thanks!
Example code:
' Set And statements.
If strProj_Name <> "" AND strProj_Mgr = "" AND strLocation = "" AND strScope = "" AND dtmRelease_Date = "" Then
strAnd = "AND dtmSubmit_Date > " & dtmSub_Date & " " _
& "AND tblProject.txtProject_Name = '" & strProj_Name & "' "
ElseIf strProj_Mgr <>"" AND strProj_Name = "" AND strScope = "" _
AND dtmRelease_Date = "" Then
strAnd = "AND dtmSubmit_Date > " & dtmSub_Date & " " _
& "AND tblUser.intUser_ID = '" & strProj_Mgr & "' "
ElseIf strLocation <>"" AND strProj_Name = "" AND strProj_Mgr = "" AND strScope = "" AND dtmRelease_Date = "" Then
strAnd = "AND dtmSubmit_Date > " & dtmSub_Date & " " _
& "AND tblProject.txtLocation = '" & strLocation & "' "
ElseIf strScope <>"" AND strProj_Name = "" AND strProj_Mgr = "" AND strLocation = "" AND dtmRelease_Date = "" Then
strAnd = "AND dtmSubmit_Date > " & dtmSub_Date & " " _
& "AND tblProject.txtLocation = '" & strLocation & "' "
ElseIf dtmRelease_Date <> "" AND strProj_Name = "" AND strProj_Mgr = "" AND strLocation = "" AND strScope = "" Then
strAnd = "AND dtmSubmit_Date > " & dtmSub_Date & " " _
& "AND tblProject.dtmRelease_Date > '" & dtmRelease_Date & "' "
ElseIf strLocation <> "" AND strProj_Name <> "" AND strProj_Mgr = "" AND strScope = "" AND dtmRelease_Date = "" Then
strAnd = "AND dtmSubmit_Date > " & dtmSub_Date & " " _
& "AND tblProject.txtProject_Name = '" & strProj_Name & "' " _
& "AND tblProject.txtLocation = '" & strLocation & "' "
ElseIf strLocation <> "" AND strProj_Name <> "" AND strProj_Mgr <> "" AND strScope <> "" AND dtmRelease_Date = "" Then
strAnd = "AND dtmSubmit_Date > " & dtmSub_Date & " " _
& "AND tblProject.txtProject_Name = '" & strProj_Name & "' " _
& "AND tblUser.intUser_ID = '" & strProj_Mgr & "' " _
& "AND tblProject.txtLocation = '" & strLocation & "' " _
& "AND tblProject.txtScope = '" & strScope & "' "" |
|
|
|
|
|
Bulding an AND statement