Only show fields with Inactive

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working with a db that I am trying to create a query that will only show
if two fields added together equal the third then to not show that line. I
hope this makes sense. Basically if we bill a client and they only pay
partial, and then possibly make another payment. We want the two fields to
equal the original billed amount or the write off amount. This is what I
have and it shows nothing:

IIf([101504]![W/O_Amt]+[101504]![Later_pay]=[101504]![Amt_due],"Active")
 
Stacey,

The IIF() function takes three parameters (a condition, True Value, False
Value)

You need to create a computed field in your query that contains all of these
parameters. The one you show below only has the condition, and the
TrueValue portions, with nothing for the "Inactive" portion. Try adding the
following to the query grid

Status: IIf(NZ([101504]![W/O_Amt], 0)+NZ([101504]![Later_pay],
0)=NZ([101504]![Amt_due], 0),"Active", "Inactive")

That should give you a value in the Status column. Notice that I have
wrapped the references to your tables fields in the NZ() function. This is
to properly handle the case where one or the other of these values is NULL.
Without using the NZ function, you end up dealing with NULLs in arithmatic
operations, which will always return a NULL. For Example: NULL + 15 = NULL
And the test for NULL = 15 will always return NULL

Once you verify that this works, you can add a criteria to the status field
to filter out the values you don't want.

HTH
Dale
 
Back
Top