IIF & DMax with a Subform ... Use Nz?

C

Crumfert

In my main form, I have a field that references the Subform. In the control
source of the field in the Main Form I have:

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

This works for displaying the Max RevNum in the field ... but when there are
no records in the SubForm, I get the #ERROR. I would like it to display the
default of "0".

It appears that I will need to use the IIF with the Nz functions ... but I
am not that familiar with access and do not know exactly how to implement
them ...
 
A

Allen Browne

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

That should work, unless your subform does not allow new records to be
created. If so, it will go completely blank when it has no records, and the
expression above will still error. Post back if that applies.
 
O

Ofer

It doesn't make sense that this syntax will work
=DMax("[RevNum]","REVS","[DrawingNumber] =
[DrawingDataSheet]![DrawingNumber] ")
It should be
=DMax("[RevNum]","REVS","[DrawingNumber] = " &
[DrawingDataSheet]![DrawingNumber] )

And using the NZ, try this
=NZ(DMax("[RevNum]","REVS","[DrawingNumber] = " &
[DrawingDataSheet]![DrawingNumber] ) ,0)
 
C

Crumfert

Thanks ... but neither of them work. Does it matter that the two Forms are
from tables linked by a one to many relationship (or is that assumed)

Ofer said:
It doesn't make sense that this syntax will work
=DMax("[RevNum]","REVS","[DrawingNumber] =
[DrawingDataSheet]![DrawingNumber] ")
It should be
=DMax("[RevNum]","REVS","[DrawingNumber] = " &
[DrawingDataSheet]![DrawingNumber] )

And using the NZ, try this
=NZ(DMax("[RevNum]","REVS","[DrawingNumber] = " &
[DrawingDataSheet]![DrawingNumber] ) ,0)

--
I hope that helped
Good luck


Crumfert said:
In my main form, I have a field that references the Subform. In the control
source of the field in the Main Form I have:

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

This works for displaying the Max RevNum in the field ... but when there are
no records in the SubForm, I get the #ERROR. I would like it to display the
default of "0".

It appears that I will need to use the IIF with the Nz functions ... but I
am not that familiar with access and do not know exactly how to implement
them ...
Click to expand...
Click to expand...
 
C

Crumfert

Thanks, but getting #NAME? error ... ???

The subform does allow new records ...




Allen Browne said:
Try:
=DMax("[RevNum]","REVS","[DrawingNumber] = " &
Nz([DrawingDataSheet].Form![DrawingNumber],0))

That should work, unless your subform does not allow new records to be
created. If so, it will go completely blank when it has no records, and the
expression above will still error. Post back if that applies.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Crumfert said:
In my main form, I have a field that references the Subform. In the
control
source of the field in the Main Form I have:

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

This works for displaying the Max RevNum in the field ... but when there
are
no records in the SubForm, I get the #ERROR. I would like it to display
the
default of "0".

It appears that I will need to use the IIF with the Nz functions ... but I
am not that familiar with access and do not know exactly how to implement
them ...
Click to expand...
Click to expand...
 
O

Ofer

Is the DrawingNumber is string or number, if its a string, it should be
=NZ(DMax("[RevNum]","REVS","[DrawingNumber] = '" &
[DrawingDataSheet]![DrawingNumber] & "'" ) ,0)

Try it in my example or the example that Allan gave you
--
I hope that helped
Good luck


Crumfert said:
Thanks ... but neither of them work. Does it matter that the two Forms are
from tables linked by a one to many relationship (or is that assumed)

Ofer said:
It doesn't make sense that this syntax will work
=DMax("[RevNum]","REVS","[DrawingNumber] =
[DrawingDataSheet]![DrawingNumber] ")
It should be
=DMax("[RevNum]","REVS","[DrawingNumber] = " &
[DrawingDataSheet]![DrawingNumber] )

And using the NZ, try this
=NZ(DMax("[RevNum]","REVS","[DrawingNumber] = " &
[DrawingDataSheet]![DrawingNumber] ) ,0)

--
I hope that helped
Good luck


Crumfert said:
In my main form, I have a field that references the Subform. In the control
source of the field in the Main Form I have:

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

This works for displaying the Max RevNum in the field ... but when there are
no records in the SubForm, I get the #ERROR. I would like it to display the
default of "0".

It appears that I will need to use the IIF with the Nz functions ... but I
am not that familiar with access and do not know exactly how to implement
them ...
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

#Name indicates that one of the names does not resolve.

First. make sure the Name of this text box is not the same as one of the
fields in the form's RecordSource. In the Properties box, Other tab, the
Name could be Text99 or somthing, but not RevNum if you have a RevNum in the
form.

Next, you must have a table Named REVS.
It must have fields named RevNum and DrawingNumber.

Next, your subform control must be called DrawingDataSheet. Open the main
form in design view, right-click the edge of the subform control, and choose
Properties. What is its Name? (Note that the Name of the subform control can
be different from its SourceObject, i.e. the name of the form loaded into
the control.)

Finally, the subform must have a control named Drawing Number.

Once you get that working when there ARE records in the subform, we can help
you handle the case where there are not if you like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Crumfert said:
Thanks, but getting #NAME? error ... ???

The subform does allow new records ...




Allen Browne said:
Try:
=DMax("[RevNum]","REVS","[DrawingNumber] = " &
Nz([DrawingDataSheet].Form![DrawingNumber],0))

That should work, unless your subform does not allow new records to be
created. If so, it will go completely blank when it has no records, and
the
expression above will still error. Post back if that applies.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Crumfert said:
In my main form, I have a field that references the Subform. In the
control
source of the field in the Main Form I have:

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

This works for displaying the Max RevNum in the field ... but when
there
are
no records in the SubForm, I get the #ERROR. I would like it to
display
the
default of "0".

It appears that I will need to use the IIF with the Nz functions ...
but I
am not that familiar with access and do not know exactly how to
implement
them ...
Click to expand...
Click to expand...
Click to expand...
 
Top