IF (Is Null function)

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

Guys I made a mistake in my other email this is the issue.
The PAID field has a P or it's blank there are no zero's
(0).

I have 4 fields

OBLIGATED COMMITTED PAID ECD TOTAL

I want the ECD Total field to include:

If the PAID field =P go to the OBLIGATED field and pick
that amount and place in the ECD TOTAL field. And if the
PAID field is blank or Null pick the COMMITTED field and
place that amount into the ECD TOTAL field.

I need a formula for the above issue.
I'm sorry to provide the incorrect amount.
 
Hi Allison,
I assume you are wanting a query to output the ECD AMOUNT.

The query below uses a table called Payments with the field names you have.

SELECT Payments.ID, Payments.Paid, Payments.Obligated, Payments.Committed, IIf([Payments]![Paid]="P",[Payments]![Obligated],[Payments]![Committed]) AS [ECD Amount]
FROM Payments;

Hope this is of some help to you.
 
I'm using a select query

I want the formula to appear after the field label name;

I used the formula below but it is not working


ECD TOTAL: IIf(IsNull([PAID])=False,[COMMITTED],IIf(IsNull
([PAID])=True,[OBLIGATED]))

I'm to provide the following:

If the PAID field =P go to the OBLIGATED field and place
that dollar amount into ECD TOTAL field. If the PAID field
= Null go to the COMMITTED field and place that dollar
amount into the ECD TOTAL field.


-----Original Message-----
Hi Allison,
I assume you are wanting a query to output the ECD AMOUNT.

The query below uses a table called Payments with the field names you have.

SELECT Payments.ID, Payments.Paid, Payments.Obligated,
Click to expand...
Payments.Committed, IIf([Payments]![Paid]="P",[Payments]!
[Obligated],[Payments]![Committed]) AS [ECD Amount]
 
Hi Allison,

The formula that you tried does not use the correct iif()
syntax. An iif() function has three arguments, the
condition, the true part and the false part. Your
formula was almost correct, you checked to see if Null
was false, specified [COMMITTED] if the condition is
true, then you nested another iif() function for the
false condition, which is fine, but the nested iif
function only has a condition and a true part, the false
part is missing. Anyway, I don't think you need the
second iif () function, you could have just listed
[Obligated] for the false part of the first iif().

But, I think that the formula that Alan posted may be
even better. His checks for the "P" rather than Null.
This may be a little better in case someone enters
something other than P in the field. Either method will
work the same if there aren't any entries other than P.

HTH, Ted Allen
-----Original Message-----
I'm using a select query

I want the formula to appear after the field label name;

I used the formula below but it is not working


ECD TOTAL: IIf(IsNull([PAID])=False,[COMMITTED],IIf (IsNull
([PAID])=True,[OBLIGATED]))

I'm to provide the following:

If the PAID field =P go to the OBLIGATED field and place
that dollar amount into ECD TOTAL field. If the PAID field
= Null go to the COMMITTED field and place that dollar
amount into the ECD TOTAL field.


-----Original Message-----
Hi Allison,
I assume you are wanting a query to output the ECD AMOUNT.

The query below uses a table called Payments with the field names you have.

SELECT Payments.ID, Payments.Paid, Payments.Obligated,
Click to expand...
Payments.Committed, IIf([Payments]![Paid]="P",[Payments]!
[Obligated],[Payments]![Committed]) AS [ECD Amount]
FROM Payments;

Hope this is of some help to you.


.
Click to expand...
.
Click to expand...
 
Back
Top