Using IIF & DMAX in a sub form

C

Crumfert

I have a Form (DrawingData) with a Subform (REVS Subform). In the main Form
is a field that is to display the Max Value of the Subform. I was using the
DMax function in the Countrol Source:

=DMax("[RevNum]","REVS","[DrawingNumber] =
[DrawingDataSheet]![DrawingNumber] ")

This works as longer as there are records in the subform. But when the
subform is empty, I get #ERROR.

I have found some info regarding the IIF function, but can't seem to figure
out how exactly to use it with my forms & with the DMax function.

Any help would be greatly appreciated. Thanks.
 
D

Dennis

Put this calculation in a hidden box and call it txtCalc for example.
In your actual box you can then set it to

=IIf(IsError(txtCalc),0,txtCalc)
 
K

Klatuu

Use the Nz function to return 0 if there are no Revs. Also, notice I changed
your DMax statement. In the original version, it would be looking for a
drawing number value that would equal the name of your control, not the value
of the control.

=Nz(DMax("[RevNum]","REVS","[DrawingNumber] = " &
[DrawingDataSheet]![DrawingNumber] ),0)
 
C

Crumfert

This seems to be working ... THANKS!

Dennis said:
Put this calculation in a hidden box and call it txtCalc for example.
In your actual box you can then set it to

=IIf(IsError(txtCalc),0,txtCalc)

Crumfert said:
I have a Form (DrawingData) with a Subform (REVS Subform). In the main Form
is a field that is to display the Max Value of the Subform. I was using the
DMax function in the Countrol Source:

=DMax("[RevNum]","REVS","[DrawingNumber] =
[DrawingDataSheet]![DrawingNumber] ")

This works as longer as there are records in the subform. But when the
subform is empty, I get #ERROR.

I have found some info regarding the IIF function, but can't seem to figure
out how exactly to use it with my forms & with the DMax function.

Any help would be greatly appreciated. Thanks.
Click to expand...
Click to expand...
 
C

Crumfert

Tried this but now getting a NAME display ...

??? The subform refers to a Table with a One to Many relationship with a
table ... linked by the field Drawing Number ... does that affect anything at
all???

Klatuu said:
Use the Nz function to return 0 if there are no Revs. Also, notice I changed
your DMax statement. In the original version, it would be looking for a
drawing number value that would equal the name of your control, not the value
of the control.

=Nz(DMax("[RevNum]","REVS","[DrawingNumber] = " &
[DrawingDataSheet]![DrawingNumber] ),0)

Crumfert said:
I have a Form (DrawingData) with a Subform (REVS Subform). In the main Form
is a field that is to display the Max Value of the Subform. I was using the
DMax function in the Countrol Source:

=DMax("[RevNum]","REVS","[DrawingNumber] =
[DrawingDataSheet]![DrawingNumber] ")

This works as longer as there are records in the subform. But when the
subform is empty, I get #ERROR.

I have found some info regarding the IIF function, but can't seem to figure
out how exactly to use it with my forms & with the DMax function.

Any help would be greatly appreciated. Thanks.
Click to expand...
Click to expand...
 
C

Crumfert

Although this works for getting rid of the #ERROR ... but if I add a record
in the SubForm, it doesn't update ... it still displays the default "0" ...
If I close the form, then reopen it, it's correct, but I need to print out
the form before closing ...
Is there some way to get this to update?

Dennis said:
Put this calculation in a hidden box and call it txtCalc for example.
In your actual box you can then set it to

=IIf(IsError(txtCalc),0,txtCalc)

Crumfert said:
I have a Form (DrawingData) with a Subform (REVS Subform). In the main Form
is a field that is to display the Max Value of the Subform. I was using the
DMax function in the Countrol Source:

=DMax("[RevNum]","REVS","[DrawingNumber] =
[DrawingDataSheet]![DrawingNumber] ")

This works as longer as there are records in the subform. But when the
subform is empty, I get #ERROR.

I have found some info regarding the IIF function, but can't seem to figure
out how exactly to use it with my forms & with the DMax function.

Any help would be greatly appreciated. Thanks.
Click to expand...
Click to expand...
 
Top