Need help with IIF statement

[mommyof4kids]

I have a date of birth (DOB) for each person in my db. I need to calculate a mandatory retirement ages for some. I have created a Yes/No checkbox to indicate if they have a mandatory retirement date.

To calculate the date I have DateAdd("yyy", 56, [DOB]) and that works great!

I only want to show the mandatory retirement date if the checkbox is checked, otherwise it would be blank (or better yet, not shown at all)

I think I need an IIF statement ?? IIF(([Checkbox]=-1, ???????

Any help is greatly appreciated!!!

[rzw0wr]

Are you working in a query, a form or a report?

Dale

[mommyof4kids]

I am working in a form

Are you working in a query, a form or a report?

Dale

[rzw0wr]

Make a query for the form.
SELECT Field1, Field2,.... FROM TableName WHERE MandatoryRetirementCheckbox = True;

Dale

[mommyof4kids]

I need to show all records, not just those that have the checkbox marked

Or how do I only show a mandatory retirement date if the checkbox is checked, otherwise show N/A ??

Make a query for the form.
SELECT Field1, Field2,.... FROM TableName WHERE MandatoryRetirementCheckbox = True;

Dale

[Bob Fitz]

Make a query for the form.
SELECT Field1, Field2,.... FROM TableName WHERE MandatoryRetirementCheckbox = True;

Dale

That will make the records where MandatoryRetirementCheckbox = False unavailable.

Perhaps it would be better to use some code, maybe:

If Me.[Checkbox]=True Then
Me.[NameOfControlToHide].Visible=True
Else
Me.[NameOfControlToHide].Visible=False

This would need to be in the forms On Current event and the After Update event of the checkbox.

[rzw0wr]

Gotcha. Sorry misread your post.
Is this a single form, a datasheet or a continuous form?

Dale

[Dal Jeanis]

If you want the field to be text, and display "N/A", then you could do this:

IIF([Checkbox], Format (DateAdd("yyyy", 56, [DOB]), "MM/DD/YYYY") , "N/A")

If you want the field to be a date field, you could do this:

IIF([Checkbox], DateAdd("yyyy", 56, [DOB]), 0)

and use conditional formatting to make 0 records show nothing when value is 0.

[mommyof4kids]

I get an error message: "The expression you entered has a function containing the wrong number of arguments"

If you want the field to be text, and display "N/A", then you could do this:

IIF(([Checkbox], Format (DateAdd("yyyy", 56, [DOB]), "MM/DD/YYYY") , "N/A")

If you want the field to be a date field, you could do this:

IIF(([Checkbox], DateAdd("yyyy", 56, [DOB]), 0)

and use conditional formatting to make 0 records show nothing when value is 0.

[mommyof4kids]

I get an error message: "The expression you entered has a function containing the wrong number of arguments"

If you want the field to be text, and display "N/A", then you could do this:

IIF(([Checkbox], Format (DateAdd("yyyy", 56, [DOB]), "MM/DD/YYYY") , "N/A")

If you want the field to be a date field, you could do this:

IIF(([Checkbox], DateAdd("yyyy", 56, [DOB]), 0)

and use conditional formatting to make 0 records show nothing when value is 0.

[mommyof4kids]

I have a main form that is more of a summary sheet for each employee (select employee from drop down list, it displays all of their information on the screen). If they need t enter/change information for that employee, I have a button on each section to open a form. In this case, to open the retirement information form..

We have some employees that have a mandatory retirement age of 56, others that do not. I added a checkbox to indicate if they are mandatory. In the query I have the date calculating for everyone to age 56. Maybe that is my error? I don't want the query to only show Age56 people because the form needs to show the same data for all employees. I would like the form to display the mandatory date only for those that have a mandatory retirement date (and I need to show the date for only those employees).

The form opens as a split form with the datasheet displaying on the bottom (they want to see all retirement actions they have processed for this employee, both ways)

[Dal Jeanis]

Sorry. I just edited to remove one of the opening parenthesis. The terms should start with

IIF([Checkbox],