Calculations

G

Guest

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
 
D

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)
 
G

Guest

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.
 
G

Guest

Thank you!! That worked perfectly!!

Owl Lover

Douglas J. Steele said:
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!)


Owl Lover said:
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
 
G

Guest

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

Jackie L said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top