DSum Query using Date

[Guest]

I have created a database which tracks inventory and calculates and monitors
turnover. In my query I have 4 columns that are put in by the user in a form
linked to a "Transactions" table: Issues, Receipts, ProeprtyNumber and
TransactionDate. I created a field (InvLvl) which is supposed to calculate
the current inventory level for each property. Here is the formula for that
cell:

InvLvl: DSum("[Issues]+[Receipts]","Transactions","PropertyNumber=" &
[PropertyNumber] & " AND TransactionDate<= #" & [TransactionDate] & "#")

This query almost always works, but in a few cases, it will ignore the first
transaction entered and only use the other transactions when calculating the
inventory level, throwing off all of my numbers. Is there a reason this is
happening that I am not seeing? If it helps, the "Issues" field must always
be negative, while the "Receipts" field is always positive. Also, I'm using
Access 2002.

 

[John Spencer]

Well, could it be that Issues or Receipts is a null value in those cases?

If either one is a null, then the calculation of issues + receipts is going to
be null.

I'm not sure this will work inside the Dsum, but you might try

DSum("Nz(Issues,0) + Nz(Receipts,0)",...)

 

[Guest]

That was it. The default value is set to 0, it accidently got erased in
those cases.

Thanks!

Well, could it be that Issues or Receipts is a null value in those cases?

If either one is a null, then the calculation of issues + receipts is going to
be null.

I'm not sure this will work inside the Dsum, but you might try

DSum("Nz(Issues,0) + Nz(Receipts,0)",...)

I have created a database which tracks inventory and calculates and monitors
turnover. In my query I have 4 columns that are put in by the user in a form
linked to a "Transactions" table: Issues, Receipts, ProeprtyNumber and
TransactionDate. I created a field (InvLvl) which is supposed to calculate
the current inventory level for each property. Here is the formula for that
cell:

InvLvl: DSum("[Issues]+[Receipts]","Transactions","PropertyNumber=" &
[PropertyNumber] & " AND TransactionDate<= #" & [TransactionDate] & "#")

This query almost always works, but in a few cases, it will ignore the first
transaction entered and only use the other transactions when calculating the
inventory level, throwing off all of my numbers. Is there a reason this is
happening that I am not seeing? If it helps, the "Issues" field must always
be negative, while the "Receipts" field is always positive. Also, I'm using
Access 2002.