Calculations

[Owl Lover]

I am working in Access 2000-2003. In my form I have the following expression:

=[RETURN_QTY]+[RETURN_QT2]+[RETURN_QT3]+[RETURN_QT4]+[RETURN_QT5]+[RETURN_QT6]

The total of all 6 textboxes is supposed to appear in the Return Quantity
Total box. I can get the total to appear if all 6 boxes have numbers in
them, but there may be times when I only have 1 or 2 boxes with totals, but I
still need the total to appear no matter how many boxes have totals. Can
someone please tell me what I'm doing wrong here?

Also, I have another box requires the Total Scrap Quantity X Parts/Scrap
Value. I have an expression as follows:

=[NCR 1]![SCRAP_QTY]*[NCR 1]![PARTS_SCRA]

Obviously this isn't correct because it doesn't work. I could use some help
with this as well.

Thanks,

Owl Lover

 

[Douglas J. Steele]

Use the Nz function to convert any boxes with Null in them to zeroes:

=Nz([RETURN_QTY],0)+Nz([RETURN_QT2],0)+Nz([RETURN_QT3],0)+Nz([RETURN_QT4],0)+Nz([RETURN_QT5],0)+Nz([RETURN_QT6],
0)

In VBA on a form, you cannot include the table name. See whether

=[SCRAP_QTY]*[PARTS_SCRA]

works (assuming those two fields are in the form's underlying RecordSource)

 

[Jackie L]

You will need to use the Null to Zero Function. Put NZ in front of each
field like:

=Nz(RETURN_QTY])+Nz([RETURN_QT2]) etc

Hope this helps.

 

[Owl Lover]

Thank you!! That worked perfectly!!

Owl Lover

Use the Nz function to convert any boxes with Null in them to zeroes:

=Nz([RETURN_QTY],0)+Nz([RETURN_QT2],0)+Nz([RETURN_QT3],0)+Nz([RETURN_QT4],0)+Nz([RETURN_QT5],0)+Nz([RETURN_QT6],
0)

In VBA on a form, you cannot include the table name. See whether

=[SCRAP_QTY]*[PARTS_SCRA]

works (assuming those two fields are in the form's underlying RecordSource)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am working in Access 2000-2003. In my form I have the following
expression:

=[RETURN_QTY]+[RETURN_QT2]+[RETURN_QT3]+[RETURN_QT4]+[RETURN_QT5]+[RETURN_QT6]

The total of all 6 textboxes is supposed to appear in the Return Quantity
Total box. I can get the total to appear if all 6 boxes have numbers in
them, but there may be times when I only have 1 or 2 boxes with totals,
but I
still need the total to appear no matter how many boxes have totals. Can
someone please tell me what I'm doing wrong here?

Also, I have another box requires the Total Scrap Quantity X Parts/Scrap
Value. I have an expression as follows:

=[NCR 1]![SCRAP_QTY]*[NCR 1]![PARTS_SCRA]

Obviously this isn't correct because it doesn't work. I could use some
help
with this as well.

Thanks,

Owl Lover

 

[Owl Lover]

Thank you so much!!! That worked perfectly!! I knew it was something simple
but I couldn't remember what it was!

You will need to use the Null to Zero Function. Put NZ in front of each
field like:

=Nz(RETURN_QTY])+Nz([RETURN_QT2]) etc

Hope this helps.

I am working in Access 2000-2003. In my form I have the following expression:

=[RETURN_QTY]+[RETURN_QT2]+[RETURN_QT3]+[RETURN_QT4]+[RETURN_QT5]+[RETURN_QT6]

The total of all 6 textboxes is supposed to appear in the Return Quantity
Total box. I can get the total to appear if all 6 boxes have numbers in
them, but there may be times when I only have 1 or 2 boxes with totals, but I
still need the total to appear no matter how many boxes have totals. Can
someone please tell me what I'm doing wrong here?

Also, I have another box requires the Total Scrap Quantity X Parts/Scrap
Value. I have an expression as follows:

=[NCR 1]![SCRAP_QTY]*[NCR 1]![PARTS_SCRA]

Obviously this isn't correct because it doesn't work. I could use some help
with this as well.

Thanks,

Owl Lover