# If and combinations

## Recommended Posts

Hello , let mi first try to show you what i am trying to do.

`date1 = Request.Form("date1")date2 = Request.Form("date2")date = Request.Form("date")value1 = Request.Form("value1")value2 = Request.Form("value2")company = Request.Form("company")If date ="" and value1 = "" and value2 = "" and company = ""   and date2="" and date1 ="" Then checking = 1  End IfIf date ="1" and value1 = "" and value2 = "" and company = "" and date1 ="" And date2=""  Then checking = 3 End IfIf  date ="2" and value1 = "" and value2 = "" and company = "" and date1 ="" And date2="" Then checking = 4 End IfIf  date ="3" and value1 = "" and value2 = "" and company = "" and date1 ="" And date2="" Then checking = 5  End IfIf date = "4" and value1 = "" and value2 = "" and company = "" and date1 ="" And date2="" Then checking = 6 End IfIf value1 = "" and value2 = "" and date1 ="" And date2="" Then checking = 7 End IfIf  company = "" and value2 = "" and date1 ="" And date2="" Then checking = 8 End IfIf  value1 = "" and company = "" and date1 ="" And date2="" Then checking = 9 End IfIf  value2 = "" and value1 = "" and company = ""  And date2="" Then checking = 10 End IfIf  date1="" and value2 = "" and value1 = "" and company = "" Then checking = 11 End If...'as you can see i am trying to write all the combinations to make a search without an error. But i have a problem with writting this if statementSelect Case checking   Case 1	sql = "SELECT * FROM tblcompany;"	   Case 2	sql = "SELECT * FROM tblcompany WHERE company_date BETWEEN "&(date1)&" AND " &(date2)&";"   Case 3  	sql = "SELECT * FROM tblcompany WHERE company_date BETWEEN "&fncleto(a)&" AND "&fncdanes(b)&" ;"   Case 4   	sql = "SELECT * FROM tblcompany WHERE company_date BETWEEN "&fncmesec(c)&" AND "&fncdanes(b)&";"	   Case 5   	sql = "SELECT * FROM tblcompany WHERE company_date BETWEEN "&fncteden(d)&" AND "&fncdanes(b)&";"	   Case 6  	sql = "SELECT * FROM tblcompany WHERE company_date = "&fncdanes(b)&" ;"	   Case 7     sql = "SELECT * FROM tblcompany WHERE company = " &company&";"     Case 8  	sql = "SELECT * FROM tblcompany WHERE company_value >= " &value1&";" 	   Case 9  	sql = "SELECT * FROM tblcompany WHERE company_value <= " &value2& ";"   Case 10     sql = "SELECT * FROM tblcompany WHERE company_date >= "&date1&";"   Case 11     sql = "SELECT * FROM tblcompany WHERE company_date <= "&date2&";"     Case 12     sql = "SELECT tblcompany.company, tblcompany.company_value FROM tblcompany WHERE (((tblcompany.company)='" & company & "') AND ((tblcompany.company_value)='" & value1 & "'));"  ...	End Select`

O.K. I have a problem with writting a corect If statement, ... Why this kind of statement, i would like to writte all of the combinations of atributs so that i would never get an eror and allways an corect SELECT would be made. For example: when nothing is chouse i get he table with all the data , and so on. I eaven would like to filtet with more combinations. This will be a filter for data that i get from the DB. date is an radiobutton,... i think that you will figer it out, but this still aren t all the combinations that i wrot , this is just a part of it,...Can someon help me with this IF statementThanks

##### Share on other sites

What if, instead, you dynamically built the WHERE part of your SQL query based on the values of the form?Since I don't know VB, here is the idea using javascript

`var wherestring = "";if (date1 != "" && date2 != ""){	wherestring = "company_date BETWEEN " + date1 + " AND " + date2;}if (company != ""){	if (wherestring != "")	{		wherestring += " AND ";	}		wherestring = "company = '" + company "'";}`

Then, when you build your SQL query, you'd do something like this:

`sql = "SELECT * FROM tblcompany"if (wherestring != ""){	sql += " WHERE " + wherestring;}`

##### Share on other sites

You can also add a "Case Else" to the switch statement which will get executed if none of the other cases match.

##### Share on other sites
You can also add a "Case Else" to the switch statement which will get executed if none of the other cases match.
HelloThanks for that! But a few minutes ago i just did it like this.Value = 0 Then Value ""If Value <> "" Then Run = 1...Select Case RunCase 1SQL = SELECT * FROM tblCompany WHERE Cost = " & Value & " ;"By using <> i can writte all the combinations that i want ,...Thanks

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.