Nested iif

[dref]

FBal: IIf([Bal2]>0,Nz(DLookUp("bal2","SummaryTotalBalanceDlast2"," AppID = " & [AppID]),Nz(DLookUp("Bal3","SummaryTotalBalanceDFirst2"," AppID = " & [AppID]))))



Can someone please help me with what's wrong with this? The first part works but the second brings nothing.
Thx

[June7]

Try removing the spaces in front of AppID. Specify alternate value for the second Nz().

FBal: IIf([Bal2]>0, Nz(DLookUp("bal2", "SummaryTotalBalanceDlast2", "AppID = " & [AppID]), Nz(DLookUp("Bal3", "SummaryTotalBalanceDFirst2", "AppID = " & [AppID]), 0)))


If you want the first DLookup value if Bal2 is > 0 but the second DLookup if Bal2 <=0, end paren is out of place.

FBal: IIf([Bal2]>0, Nz(DLookUp("bal2", "SummaryTotalBalanceDlast2", "AppID = " & [AppID]), 0), Nz(DLookUp("Bal3", "SummaryTotalBalanceDFirst2", "AppID = " & [AppID]), 0))

[dref]

SummaryTotalBalanceFINAL is the query that should show actual balances.

Idea:
If someone has paid part of the loan, their balance in the balances report(a query from SummaryTotalBalanceFINAL) should show the last balance(Endingbalance) after the last payment. But if someone hasn't paid anything, their balance should be the beginning balance.
Thx

[pbaldy]

The sample db doesn't open for me.

[dref]

Which office are you using?

[dref]

Please See the new attachment.
I have converted it to access 2007
Thanks.

[pbaldy]

Maybe it's me, but that won't open for me either. See if it will upload without zipping (compact-repair first).

[ssanfu]

The dB in Post #6 also will not open for me......

[June7]

Are you using Windows Compression to zip the file?

[dref]

Yes Please.
If i dont compress its above 500kbs so it will not upload.
See this one now and we see if it opens.
thx

[pbaldy]

Don't think I've ever had this much trouble, but that one won't open for me either. They all open as if I'd just started Access by itself.

[June7]

Same here. Just opens Access app. Not even a 'cannot open' or 'not compatible' message.

[ssanfu]

What version of Access are you using?
Your tag line says you have A2003 - the dB should be a MDB.
When I extract the file from the zip, it has an accdb extension.

[dref]

How about if you opened access, create a new db and then import into the new db?
It might work that way.

[ssanfu]

Tried that yesterday before I posted.

Once again:
What version of Access are you using?
Your tag line says you have A2003 - the dB should be a mdb extension.
When I extract the file from the zip, it has an accdb extension.