IIF complications, Plz Help.

[Mr. Hero]

I need assitance with my IIF statement.

IIf([Reports]![RptMaster]![ChkBxs]="1",True,IIf([Reports]![RptMaster]![ChkBxs]="2",False,IIf([Reports]![RptMaster]![ChkBxs]="3",,)))

I have a column in my table that stores values from a check box in my form.

I have a form that has a option group in it, and it has 3 buttons to select from. Depending on the value, you can get 1, 2 or 3 as your value; hence my IIF statement. Selection "1" will allow my report to filter my data for only checked boxes to display on my report. Selection "2" will allow my report to filter my data for only unchecked boxes to display on my report. Selection "3" is suppose to show all of them, but I am having dificulty with this. Can anyone help me figure this out?

Thanks in Advance.

 

[Simon_MT]

When you call the report you could try creating a Function

Private Function ReportCriteria()

    With CodeContextObject ' Form with Option Group
        If .[Option] = 1 Then
            ReportCriteria = "[chkbxs] = True"
        ElseIf .[Option] = 2 Then
            ReportCriteria = "[chkbxs] = False"
        End If
    End With    
End Function

Then when you call the Report put the Criteria in the Where condtion:

DoCmd.OpenReport "Report Name", acViewPreview, "", ReportCriteria, acWindowNormal

Simon

 

[Mr. Hero]

Simon,

I have attached a sample of what I have done so far. Would you mind looking at the attachment and assist me with this?

Thanks a bunch mate.

Mario

 

[boblarson]

1. how do you determine it is complete?

2. You have it a bit twisted around. It isn't the form that is opened by the report it should be the report opened by the form.

3. I have made some changes to it but am waiting on your input as to how to determine it is complete.

4. You should use an Option group for the toggles. I put one on your form and you can format it all nice like you did before. But the benefit of the option group is that it will only let one option be selected with the toggle, and it can be queried for its value easily enough.

 

[boblarson]

Okay, I see now in your table you have a checkbox for COMPLETE. But I'm not understanding why you are trying to use a subreport and not just the main report since they are both based on the same table.

 

[boblarson]

Here's your stuff revised. Hopefully it will work the way you want it, or at least you'll learn from it.

 

[Mr. Hero]

Bob,

Your solution is absolutely perfect!

A million thanks.

Mario

 

[boblarson]

Glad to hear.

 

[Mr. Hero]

Bob,

I made a few changes to the operation of the DB. I came accross a few different issues. One, I have 4 sub reports that are inside the Main report (RptMaster). Two, I tried to modify your suggestion to fit the changes, and I failed . Would you mind helping me with this change?

I added a drop down menu to my Date Range form. Inside this I have an If,elseif, then statement to make sure that the proper report opens.(RptMaster). The subreports inside the Master report need to populate based off the Date Range parameters.

Please let me know if you need sample of the new changes.

Thanks in advance for you assitance.

 

[boblarson]

You can either

1. Reference the form's date fields from the subreports' queries or

2. You can use a QueryDef in code to set the where clause (easily done using the ReplaceWhereClause in the SQL Tools that Access MVP Armen Stein has generously provided. I use that myself.

 

[Mr. Hero]

Thanks Bob!