Balance Comparison Query

[Natalie]

I need some help with this query/report. My query does a comparison between
records in current month and records in previous month. It basically takes
active account numbers and balances for current month and compares it to
active accounts and balances from previous month. I am having some
difficulty when I try to subtract on an account which only exist in one month
(either previous or current.)

I use this if statement to do my calculation:

Balance Difference = iif(CurrentMth.AccountNr = “â€,
PreviousMth.Balance,iff(PreviousMth.AccountNr= “â€,
CurrentMth.Balance,CurrentMth.Balance – PreviousMth.Balance))

This works great if the account is active both current month and previous
month, but where the account only exist in one month I get a blank result.

I want the query to return the difference even if the account only exist in
one month.

Thanks for all your help!

Nats

 

[Duane Hookom]

Try something like:
Balance Difference = IIf(IsNull(CurrentMth.AccountNr),
PreviousMth.Balance,IIf(IsNull(PreviousMth.AccountNr),
CurrentMth.Balance,CurrentMth.Balance – PreviousMth.Balance))

Keep in mind that No Value and Null are not the same as "". You may also
want to convert Nulls to zero or some other values with
Nz([PossiblyNullField],0)

 

[Natalie]

Thanks a bunch! That worked great.

Try something like:
Balance Difference = IIf(IsNull(CurrentMth.AccountNr),
PreviousMth.Balance,IIf(IsNull(PreviousMth.AccountNr),
CurrentMth.Balance,CurrentMth.Balance – PreviousMth.Balance))

Keep in mind that No Value and Null are not the same as "". You may also
want to convert Nulls to zero or some other values with
Nz([PossiblyNullField],0)

--
Duane Hookom
Microsoft Access MVP

I need some help with this query/report. My query does a comparison between
records in current month and records in previous month. It basically takes
active account numbers and balances for current month and compares it to
active accounts and balances from previous month. I am having some
difficulty when I try to subtract on an account which only exist in one month
(either previous or current.)

I use this if statement to do my calculation:

Balance Difference = iif(CurrentMth.AccountNr = “â€,
PreviousMth.Balance,iff(PreviousMth.AccountNr= “â€,
CurrentMth.Balance,CurrentMth.Balance – PreviousMth.Balance))

This works great if the account is active both current month and previous
month, but where the account only exist in one month I get a blank result.

I want the query to return the difference even if the account only exist in
one month.

Thanks for all your help!

Nats