Dlookup

Try this -


Available: iif([Guarantor Description] like "DNB*",  Dlookup("[FacilityAmount]","tblBankFacilityAmts", "[BankName_Facility] ="  & 179 – Sum([Approved Amount In USD Equivalent]),0)

If 179 doesn't change put it inside the quotes.

Assuming that's the criteria for [BankName_Facility] of course.

There's a few other things that aren't quite right, well it's just one really - the "s around [Guarantor Description]

Anyway, try this:


Available: IIf( [Guarantor Description] LIKE "DNB*", Dlookup("[FacilityAmount]","tblBankFacilityAmts", "[BankName_Facility] =179" – Sum([Approved Amount In USD Equivalent],0)

Or this (with parentheses for grouping):

Available: iif([Guarantor Description] like "DNB*",  Dlookup("[FacilityAmount]","tblBankFacilityAmts", "[BankName_Facility] ="  & (179 – Sum([Approved Amount In USD Equivalent])),0)

Try this:

Available: IIf([Guarantor Description] Like "DNB*", DLookup("[FacilityAmount]", "tblBankFacilityAmts", "[BankName_Facility] = " & 179 - Sum([Approved Amount In USD Equivalent]), 0))

which does compile in vba code.

mx

Imnorie:
had a syntax
Available: IIf( [Guarantor Description] LIKE "DNB*", Dlookup("[FacilityAmount]","tblBankFacilityAmts", "[BankName_Facility] =179" – Sum([Approved Amount In USD Equivalent],0)

Mbizup:
had a syntax (it highlighted the "-" sign

Available: iif([Guarantor Description] like "DNB*",  Dlookup("[FacilityAmount]","tblBankFacilityAmts", "[BankName_Facility] ="  & (179 – Sum([Approved Amount In USD Equivalent])),0)

MX:
no syntax  but it said "contained wrong number of arguments":
Available: IIf([Guarantor Description] Like "DNB*", DLookup("[FacilityAmount]", "tblBankFacilityAmts", "[BankName_Facility] = " & 179 - Sum([Approved Amount In USD Equivalent]), 0))

OK this:

IIf([Guarantor Description] Like "DNB*", DLookup("[FacilityAmount]", "tblBankFacilityAmts", "[BankName_Facility] = " & 179 - Sum([Approved Amount In USD Equivalent])), 0)

mx

The bits I changed added: closing parenthesis for the DLookup, removed the ,0 from Sum - it only takes one argument/expression.

Imnorie:  that was the solution.

thank you

pdvsa,  How can that work since the 2nd argument for IIF is missing ... namely  ,0)
What I posted first should also work ... @ http:#a37051100

?

mx,

That Value If False argument is optional if it is used in an SQL statement.

Both you and I had missing and/or misplaced parentheses.

I adjusted it.  The main pt of it was the minus "-" sign part.   Your answer gave me 0.  It seemed to be the opposite of Imnories.  If I removed the 0 then it did not return any result.

MX

It certainly seemed to work and Access didn't complain about the missing falsepart.

It also works with the falsepart.

IIf([Guarantor Description] Like "DNB*",DLookUp("[FacilityAmount]","tblBankFacilityAmts","[BankName_Facility] =179")-Sum([Approved Amount In USD Equivalent]), 0)

If may be optional, BUT ... not if you really wanted the zero.

And my last post has no missing parens, as it compiles in vba just fine.  Try it ...

mx

mx

I don't know why but I don't think the sum of the [Approved Amount in USD Equivalent] is meant to get subtracted from 179 to get the criteria for [BankName_Facility] in the DLookUp.

mx,

Here's the issue:

<< then I need to lookup the FacilityAmount in tblBankFacilityAmts then subtract the SUM [Approved Amount In USD Equivalent]>>

The Sum needs to be subtracted from the DLookup result - not from the criteria (that is where your posts and mine are off).