calculating totals

[rcollins]

I ahve a database that I put together to keep track of the office supplies. We input what has been purchased and what goes out. When I run a report, usually it is just for a month, but I have a calculation issue with this. If I run the report for the whale time we have used this database, the units on hand is correct. When I run it for specific dates, it only calculates the on hand for these times, throwing off the total of on hand supplies. I understand why it is calculating this way, but even if I run the report for just Jan, I need the total on hand to calculate from all of the entries not just Jan.
Access 2003 on xp machine
here is where I get my current on hand amount (on my form this total is right because it is pulling from all of the records)

=(Nz([Standard])+Sum(Nz([UnitsOrdered]))-Sum(Nz([UnitsUsed])))

[jaxjagfan]

I ahve a database that I put together to keep track of the office supplies. We input what has been purchased and what goes out. When I run a report, usually it is just for a month, but I have a calculation issue with this. If I run the report for the whale time we have used this database, the units on hand is correct. When I run it for specific dates, it only calculates the on hand for these times, throwing off the total of on hand supplies. I understand why it is calculating this way, but even if I run the report for just Jan, I need the total on hand to calculate from all of the entries not just Jan.
Access 2003 on xp machine
here is where I get my current on hand amount (on my form this total is right because it is pulling from all of the records)

=(Nz([Standard])+Sum(Nz([UnitsOrdered]))-Sum(Nz([UnitsUsed])))

It sounds like you want the totals as of Jan. In your criteria for the date:

<=1/31/2008

This would give you the totals for all data through January 2008.

[rcollins]

not really, see I have criteria date input for start and finish dates, so the report we will run is from 1/1/08 to 1/31/08 which the user enters, but then it gives me an on hand total that only uses the used and ordered amounts for this time period throwing off the on hand total. I only want the report to show the transactions for this period, but want the on hand total to calculate from all of the records, which date back to 7/1/06.

[jaxjagfan]

not really, see I have criteria date input for start and finish dates, so the report we will run is from 1/1/08 to 1/31/08 which the user enters, but then it gives me an on hand total that only uses the used and ordered amounts for this time period throwing off the on hand total. I only want the report to show the transactions for this period, but want the on hand total to calculate from all of the records, which date back to 7/1/06.

On hand would be:

=DSum("[Standard]", "yourtablename")+DSum("[UnitsOrdered]", "yourtablename")-DSum("[UnitsUsed]","yourtablename")

If these columns are numeric values but some records may have null values, why don't you do an update query to set the Nulls to 0 so you don't have to worry about using "NZ". You can also set the default value of the columns to 0 so any future records don't have nulls in them.