IIf Statement in Criteria

[gheyman]

I have a table with 5 fields (not counting the ID). The last four fields will have either a "YES" in it or the field will be blank



A record will have various combinations of Yes and Blanks and some could be all Yes but none will have all blanks.

The First field is a list of unique Tasks and the other Four columns/fields represent depratments. (contracts dept, Pricing Dept, Proposal Mgmt Dept....)

Some of the tasks in the db are required by more than one group (some are required by all)

One form I have five Toggles (representing the four departments and one for all)

I want to use the Toggle condition (True/False) in the Query Criteria. If the user clicks the Contracts, the results should only show the tasks where the Contracts Field has a "Yes" in for the record. If the User Selects the Contracts and Pricing toggles, then I want the query to show all the Task for both departments where they are "Yes" values. And obviously if the All Toggle is set to True show all Tasks.

I assume an IIF statement is the answer but I cannot think through the complete formula without hitting roadblocks. I cannot do nested if's on each because the formula will stop at the first one where the condition is true.

Like IIF([ToggleContracts]=Tue, "YES",.....

Any suggestion?

I thought of making the table only three fields and putting the words Contracts, Pricing... in it if the Task was required - then trying to use the *Pricing* function but could think that formula all the way through.

[Bulzie]

Not sure the IIF will work for you, probably have to build your own Where clause depending on what they enter. Then update your RecordSource with the new sql line. In the AfterUpdate event of each combo box or search field, put = QueryDate()

Can use this example http://allenbrowne.com/ser-62.html

Or below is an example I have used with various types of search field and also fuzzy searching fields with *. I removed a few lines so might not work asis but gives you the idea.


Public Function QueryData()

Dim strSQL As String
Dim strWhere As String
Dim x As Variant
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim strsqlproject As String, strsqlrisk As String, strsqlstrategy As String

'Create "Where: statement from all the criteria boxes that they enter data into
'on the form.

If Not IsNull(Me!ProjectID) Then strWhere = strWhere & " AND ProjectID=" & Me!ProjectID
If Not IsNull(Me!ProjectDescription) Then strWhere = strWhere & " AND ProjectDescription Like '*" & Me!ProjectDescription & "*'"
If Not IsNull(Me!RiskStatusDateLT) Then strWhere = strWhere & " AND RiskStatusDate<=#" & Me!RiskStatusDateLT & "#"
If Not IsNull(Me!RiskStatusDateGT) Then strWhere = strWhere & " AND RiskStatusDate>=#" & Me!RiskStatusDateeGT & "#"
If Not IsNull(Me!RiskAvoidedDateLT) Then strWhere = strWhere & " AND RiskAvoidedDate<=#" & Me!RiskAvoidedDateLT & "#"
If Not IsNull(Me!RiskAvoidedDateGT) Then strWhere = strWhere & " AND RiskAvoidedDate>=#" & Me!RiskAvoidedDateGT & "#"
If Not IsNull(Me!Results) Then strWhere = strWhere & " AND Results Like '*" & Me!Results & "*'"
If Not IsNull(Me!StatusDateLT) Then strWhere = strWhere & " AND StatusDate<=#" & Me!StatusDateLT & "#"
If Not IsNull(Me!StatusDateGT) Then strWhere = strWhere & " AND StatusDate>=#" & Me!StatusDateGT & "#"

'Create SQL statement
strsqlproject = "Select ProjectID, ProjectName from qryAllData"

' 'Trim leading " AND " from the where clause
If Not IsNull(strWhere) And strWhere <> "" Then
strsqlproject = strsqlproject & " WHERE " & Mid$(strWhere, 6)
End If

strsqlproject = strsqlproject & " GROUP BY qryAllData.ProjectID, qryAllData.ProjectName"

strsqlproject = strsqlproject & " ORDER BY qryAllData.ProjectName"

Forms![frmMain]![frmQuery].Form![ProjectList].RowSource = strsqlproject
Forms![frmMain]![frmQuery].Form![ProjectList].Requery


End Function

[gheyman]

Thanks. Much appreciated

What would happen with a formula like this;

Like IIF([ToggleAll]=True,"YES",IIF([ToggleContract]=True,"YES","")

Would the last criteria of "" show all the Records or Just the records where the field was Null ""? Or does the "" mean do nothing?

[aytee111]

The Like seems inappropriate here, rather use =

Then write it out in a sentence to see which records will be displayed:
If ToggleAll is true, then show records with field="YES"
otherwise, if ToggleContract is true, show yes's, otherwise show "" (field=blank)
If ToggleAll is not true, don't show

[gheyman]

I am confussed I thought if ToggleAll is not True then ToggleContract........

Not if Toggle is Not True then don't show.

The True condition of the If statement is the "YEs" and the False is the second If Statement - No?

[aytee111]

You're right, my bad! These statements can get confusing, hence my advice to say it out loud - I should heed it myself, lol.

[Bulzie]

I think I see where you guys are going..

So in the ToggleAll column criteria put: IIF([ToggleAll]=True,"YES", null)
Then in the next criteria row(making it an OR statement)
In the ToggleContract column put IIF([ToggleContract]=True,"YES", null)
In the TogglePricing column put IIF([TogglePricing]=True,"YES", null)
do for rest of fields all on the same criteria row to make these AND conditions
So in general would read If ToggleAll is YES then pull all records OR if ToggleContract is YES or Null AND togglePricing is YES or Null .. Pull those records

Another thing is on the form, if they select ToggleAll as Yes, in the AfterUpdate event of that field you can set the other search boxes to null just to keep it clean and make sure your code does not get weird. Also can go the other way setting toggleAll to Null if they select any of the other boxes.