Trouble writing a condition statement

[Novice1]

I'm trying to write an IIF but I'm having trouble because of the nested form and the one to many query I'm using (see attached graphic).

I have a form (frmTrackerPCS) with a subform (frmMissingItems_Subform)

On my main form (frmTrackerPCS) I want to show a label if MissingID (41) from tblMissingItems doesn't have a DateClosed from tblMissingJunction.

Here's my attempt ...

IIF(Forms!frmTrackerPCS!frmMissingItems_Subform!MissingID = "41" And Forms!frmTrackerPCS!frmMissingItems_Subform!DateClosed = Null, “Cancel”,”Open”)

 

[Ranman256]

You don't = null,
It's: IS NULL

 

[Novice1]

I have a syntax error. I believe it's how I'm referencing the subforms

=IIF(Forms!frmTrackerPCS!frmMissingItems_Subform!MissingID = "41" And Forms!frmTrackerPCS!frmMissingItems_Subform!DateClosed = Is Null, “Cancel”,”Open”)

 

[JHB]

Try the below:

=IIF(Forms!frmTrackerPCS!frmMissingItems_Subform!MissingID = "41" And IsNull(Forms!frmTrackerPCS!frmMissingItems_Subform!DateClosed), "Cancel","Open")

Be aware of the spaces the forum adds.

 

[Minty]

Remove the = before the Is Null
And if MissingID is a number, not text, take out the quotes around it.

 

[Novice1]

I'm still having problems, so I took a different approach.

I have a text on the form called LabelCancel. I want the label to appear when a condition exits (placed in OnCurrent property of the form). I have no problems if I reference a field from the form, such as this

If (Forms!frmTrackerPCS!Dependents = 0) Then
Me.LabelCancel.Visible = True
Else
Me.LabelCancel.Visible = False
End If

However, that's not what I want. I want to reference a field from the subform. I'm using the same syntax (I think). The following doesn't work but I cannot figure out why.


If (Forms!frmTrackerPCS!frmMissingItems_Subform!MissingItem = "Retainability Letter") Then
Me.LabelCancel.Visible = True
Else
Me.LabelCancel.Visible = False
End If

 

[JHB]

Post you database with some sample data, zip it.

 

[Minty]

If frmTrackerPCS is the form you are on you don't need to use the full form qualifier.

So Forms!frmTrackerPCS!Dependents becomes Me.Dependents.

And the sub form would be Me!frmMissingItems_Subform.Form!MissingItems

Bookmark this page for future reference http://access.mvps.org/access/forms/frm0031.htm