Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculation on form (2003)

    Can anyone help with a calculation on my entry form? I have a subform with Qty Received (based on it's own query), and a subform with Qty Issued (based on another query). Under that, there is a subform that is supposed to say the Total in Stock (third query), which takes Qty Received - Qty Issued for each line item. It works if I fill in a Qty received, then fill in Qty Issued, then the calcuation works. But if I have a Qty Received, but nothing is issued, it doesn't put in the amount from Qty Received as the total in stock. I put an Iif statement in the "Total in Stock" query and it seems to work in the query, but when I enter into the forms it doesn't update correctly.

    I will attach the database if anyone can help. Thanks!
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: calculation on form (2003)

    Have a look at the attachment and see if that is right. I have restructured your queries a bit, but the critical thing is the use of the NZ function. This converts Null values into Zeros. But the results of the NZ function is a string, so I then use CLng to convert back to a long integer.
    Attached Files Attached Files
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation on form (2003)

    This is exactly what I wanted to do. I did try the Nz function but I didn't have it written right or the conversion back to long integer. Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •