Help with IIF / DLookup formula

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
 
D

Duane Hookom

Try place quotest around your first argument:
=DLookUp("[PymtTotal]","q_PymtTotalsBySessionSub","[FamilyID]=" & [Family_ID])
 
S

Sandy

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...
 
D

Duane Hookom

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...
 
J

John Spencer

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...
 
S

Sandy

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...
 
D

Duane Hookom

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with iif stmt in text box 3
Error# in form using IsNull & Nz 1
Conditional Expressions 10
Need to make IIF stmt work in text box with expression 3
Stopping #Error message with Dlookup formula 2
DLookUp with Nz Function 3
Division by Zero Error 1
Access combination of IIf and Sum functions needed... Please help! 2

Top