Subtracting 2fields from 1 field in query

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

Hello,
I have separate queries that display the sum of: Total Credits, Bad Credits,
and Closed credits. I am creating a new query(to put everything together)
based off these queries. I want to subtract the bad credits and closed
credits from total credits- the credit amounts are (-) negative. So
technically I need to sum the negative of these credits. I have the following
equation in my expression:
Adj Credit Amt:[Total Credits]+-[bad credits]+-[closed credits]
This does work too, but only if bad credits and closed credits have a dollar
figure in them. For example:
Total Credits Bad Credits Closed Credits
Adj Credit Amt
-50000 -10000 -500
-39500
-40000 -20000 (blank)
(blank)

If the closed credit doesn't have anything in, it doesn't populate my Adj
Credit Amt column with the correct figures. Is there a way around this? I was
thinking maybe an IIf statement but ain't sure of my true/false parts. Can
someone please help?
Thanks, Jennifer
 
Jennifer said:
Hello,
I have separate queries that display the sum of: Total Credits, Bad
Credits,
and Closed credits. I am creating a new query(to put everything together)
based off these queries. I want to subtract the bad credits and closed
credits from total credits- the credit amounts are (-) negative. So
technically I need to sum the negative of these credits. I have the
following
equation in my expression:
Adj Credit Amt:[Total Credits]+-[bad credits]+-[closed credits]
This does work too, but only if bad credits and closed credits have a
dollar
figure in them. For example:
Total Credits Bad Credits Closed Credits
Adj Credit Amt
-50000 -10000 -500
-39500
-40000 -20000 (blank)
(blank)

If the closed credit doesn't have anything in, it doesn't populate my Adj
Credit Amt column with the correct figures. Is there a way around this? I
was
thinking maybe an IIf statement but ain't sure of my true/false parts. Can
someone please help?
Thanks, Jennifer
Click to expand...


You can do it with an IIf() statement, for example ...

IIf(IsNull([Total Credits]), 0, [Total Credits]) + -IIf(IsNull([Bad
Credits]), 0, [Bad Credits]) etc ...

.... or you can use the NZ() function ...

NZ([Total Credits], 0) + -NZ([Bad Credits], 0) etc.

The NZ function is a member of the Access object library, which means that
if your query is run in Access it will work just fine, but if you ever need
to call that query from code outside of the the Access environment, such as
a .NET app, a VB classic app, or from VBA code in another Office app such as
Excel or Word, it would not work. If that's not an issue for you, though,
using the NZ function makes for a shorter, simpler expression.
 
Thank You very much Brendan. I ended up using the IIf statement you
recommended. The problem is, I kinda know what I need to do, but with Access
being so sensitive, I always have trouble executing what I want done. I did
not try the other post you recommended because the iif stmt. worked, but I'm
sure what would've as well.
Thank You again,
Jennifer

Brendan Reynolds said:
Jennifer said:
Hello,
I have separate queries that display the sum of: Total Credits, Bad
Credits,
and Closed credits. I am creating a new query(to put everything together)
based off these queries. I want to subtract the bad credits and closed
credits from total credits- the credit amounts are (-) negative. So
technically I need to sum the negative of these credits. I have the
following
equation in my expression:
Adj Credit Amt:[Total Credits]+-[bad credits]+-[closed credits]
This does work too, but only if bad credits and closed credits have a
dollar
figure in them. For example:
Total Credits Bad Credits Closed Credits
Adj Credit Amt
-50000 -10000 -500
-39500
-40000 -20000 (blank)
(blank)

If the closed credit doesn't have anything in, it doesn't populate my Adj
Credit Amt column with the correct figures. Is there a way around this? I
was
thinking maybe an IIf statement but ain't sure of my true/false parts. Can
someone please help?
Thanks, Jennifer
Click to expand...


You can do it with an IIf() statement, for example ...

IIf(IsNull([Total Credits]), 0, [Total Credits]) + -IIf(IsNull([Bad
Credits]), 0, [Bad Credits]) etc ...

.... or you can use the NZ() function ...

NZ([Total Credits], 0) + -NZ([Bad Credits], 0) etc.

The NZ function is a member of the Access object library, which means that
if your query is run in Access it will work just fine, but if you ever need
to call that query from code outside of the the Access environment, such as
a .NET app, a VB classic app, or from VBA code in another Office app such as
Excel or Word, it would not work. If that's not an issue for you, though,
using the NZ function makes for a shorter, simpler expression.
Click to expand...
 
Back
Top