Help with IIF / DLookup formula

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

Hello -

I am getting an error with this formula:

=IIf(NZ(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]="
& [Family_ID])))

The error only shows if there is no payment data in the
q_PymtTotalsBySessionSub query. If there is data, then the formula returns
"0", which is incorrect.

This formula works properly if there is payment data, but gives an error if
there is none:
=DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])

I am trying to return a value of zero if there is no payment data for that
session.

Many thanks for your help!
Sandra
 
Try place quotest around your first argument:
=DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])
 
I tried -

=IIf(nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])))

but still get the error in the session where there is no PymtTotal in the
query. The calculations for the other sessions work.

s-

Duane Hookom said:
Try place quotest around your first argument:
=DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Sandy said:
Hello -

I am getting an error with this formula:

=IIf(NZ(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]="
& [Family_ID])))

The error only shows if there is no payment data in the
q_PymtTotalsBySessionSub query. If there is data, then the formula returns
"0", which is incorrect.

This formula works properly if there is payment data, but gives an error if
there is none:
=DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])

I am trying to return a value of zero if there is no payment data for that
session.

Many thanks for your help!
Sandra
Click to expand...
Click to expand...
 
It still looks like you missed the quotes in the second DLookup(). Also, I
would expect to see a boolean type expression in the first argument of the
IIf(). The first argument of your IIf() is:
nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0)

I would expect to see something like:
nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0)=0

Try a full expression like:
=IIf(IsNull(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,
DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID]))

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Sandy said:
I tried -

=IIf(nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])))

but still get the error in the session where there is no PymtTotal in the
query. The calculations for the other sessions work.

s-

Duane Hookom said:
Try place quotest around your first argument:
=DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Sandy said:
Hello -

I am getting an error with this formula:

=IIf(NZ(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]="
& [Family_ID])))

The error only shows if there is no payment data in the
q_PymtTotalsBySessionSub query. If there is data, then the formula returns
"0", which is incorrect.

This formula works properly if there is payment data, but gives an error if
there is none:
=DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])

I am trying to return a value of zero if there is no payment data for that
session.

Many thanks for your help!
Sandra
Click to expand...
Click to expand...
Click to expand...
 
It looks as if the whole thing can be shortened to

NZ(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sandy said:
I tried -

=IIf(nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]="
& [Family_ID])))

but still get the error in the session where there is no PymtTotal in the
query. The calculations for the other sessions work.

s-

Duane Hookom said:
Try place quotest around your first argument:
=DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Sandy said:
Hello -

I am getting an error with this formula:

=IIf(NZ(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]="
& [Family_ID])))

The error only shows if there is no payment data in the
q_PymtTotalsBySessionSub query. If there is data, then the formula
returns
"0", which is incorrect.

This formula works properly if there is payment data, but gives an
error if
there is none:
=DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])

I am trying to return a value of zero if there is no payment data for
that
session.

Many thanks for your help!
Sandra
Click to expand...
Click to expand...
Click to expand...
 
If I put the quotes around the [PymtTotal] in the second DLookup, then I get
the same value showing up as the result for every session.

This is the query and the result for FamilyID 5615

SELECT t_FamilySessionPayments.FamilyID, t_FamilySessionPayments.Session,
Sum(t_FamilySessionPayments.PaymentAmount) AS PymtTotal
FROM t_FamilySessionPayments
GROUP BY t_FamilySessionPayments.FamilyID, t_FamilySessionPayments.Session;


FamilyID Session PymtTotal
5615 Fall07 $196.25
5615 Spring08 $50.00

Since they had only credits but no payments in the Winter08 session, I want
to display a zero in the PymtTotal field of the report.

This formula below still shows $Error in the the PymtTotal field of the
report for the Winter08 session.

=IIf(IsNull(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID] And "[Session]=" &
[Session])),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID] And "[Session]=" & [Session])))

I have come up with a simple workaround:

=IIf(IsError([Payments]),0,[Payments])

Thanks!
sandra

Duane Hookom said:
It still looks like you missed the quotes in the second DLookup(). Also, I
would expect to see a boolean type expression in the first argument of the
IIf(). The first argument of your IIf() is:
nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0)

I would expect to see something like:
nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0)=0

Try a full expression like:
=IIf(IsNull(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,
DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID]))

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Sandy said:
I tried -

=IIf(nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])))

but still get the error in the session where there is no PymtTotal in the
query. The calculations for the other sessions work.

s-

Duane Hookom said:
Try place quotest around your first argument:
=DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hello -

I am getting an error with this formula:

=IIf(NZ(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]="
& [Family_ID])))

The error only shows if there is no payment data in the
q_PymtTotalsBySessionSub query. If there is data, then the formula returns
"0", which is incorrect.

This formula works properly if there is payment data, but gives an error if
there is none:
=DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])

I am trying to return a value of zero if there is no payment data for that
session.

Many thanks for your help!
Sandra
Click to expand...
Click to expand...
Click to expand...
 
I am still confused why you are querying a field using DLookup() without
identifying a single record from your query. The first field name should be
in quotes unless this is code where [PymtTotal] stores a field name and not
the total.

Also, you never suggested which value should be returned based on your
expression since there are two records with a FamilyID of 5615.

You should have been able to implement John's expression.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Duane Hookom said:
It still looks like you missed the quotes in the second DLookup(). Also, I
would expect to see a boolean type expression in the first argument of the
IIf(). The first argument of your IIf() is:
nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0)

I would expect to see something like:
nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0)=0

Try a full expression like:
=IIf(IsNull(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,
DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID]))

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Sandy said:
I tried -

=IIf(nz(DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID]),0),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])))

but still get the error in the session where there is no PymtTotal in the
query. The calculations for the other sessions work.

s-

Duane Hookom said:
Try place quotest around your first argument:
=DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hello -

I am getting an error with this formula:

=IIf(NZ(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" &
[Family_ID])),0,(DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]="
& [Family_ID])))

The error only shows if there is no payment data in the
q_PymtTotalsBySessionSub query. If there is data, then the formula returns
"0", which is incorrect.

This formula works properly if there is payment data, but gives an error if
there is none:
=DLookUp([PymtTotal],"q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])

I am trying to return a value of zero if there is no payment data for that
session.

Many thanks for your help!
Sandra
Click to expand...
Click to expand...
Click to expand...
 
Back
Top