Making a Label Visable when a conditon is met.

[Crime Inv]

I have a Form, which has a "Continuous Form" Subform. I would like
to have a label with the words "Item Out" be visible on the main Form
when a field named "Date Out" on the Subform is empty. There could be
one or more records in the Subform that could have the "Date Out"
field empty. I would like the label to visible if any record on the
Subform has the "Date Out" field empty, otherwise I'd like the label to be
hidden.

Any help would be appreciated.

Thanks.

 

[Allen Browne]

Place a text box on the main form, with this controlsource:

=IIf(IsNull(DLookup("SubID", "SubTable",
"([Date Out] Is Null) AND ([FK] = " & Nz([MainID],0)),
Null, "Item Out")

SubID = primary key of subform's table;
SubTable = name of subform's table;
FK = foreign key in subform's table;
MainID = primary key of main form's table.

For an explanation of DLookup(), see:
Getting a value from a table
at:
http://allenbrowne.com/casu-07.html

 

[Crime Inv]

Hi Allen,

Thanks for the response. I tried your code and rec'd an error message which
read "The expression you entered has a function containing the wrong number of
arguments." The following is the code I placed in the text box controlsource.

=IIf(IsNull(DLookup("coc_seq_nbr", "tbl_coc_tracking",
"([coc_in_date] Is Null) AND ([coc_subfrm_barcode] = " &
Nz([coc_barcode_nbr],0)),
Null, "Item Out")

Can you spot a problem?
Thanks,
Dave

 

[Allen Browne]

There was a bracket missing where the barcode number is concatenated into
the 3rd argument:

=IIf(IsNull(DLookup("coc_seq_nbr", "tbl_coc_tracking",
"([coc_in_date] Is Null) AND ([coc_subfrm_barcode] = " &
[coc_barcode_nbr] & ")")), Null, "Item Out")

 

[Crime Inv]

Thanks, that works fine.

However, is there a way to add two more conditions? Besides the [coc_in_date]
being NULL, I'd also like to select records in the [coc_purpose] field on the
subform which are equal to "discovery copy" and "da copy".

Thanks again in advance,
Dave

 

[Allen Browne]

Yes. You can nest Iif() statements, or use Switch(). See help for examples.