[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dlookup

Posted on 2011-10-29
16
Medium Priority
?
440 Views
Last Modified: 2012-05-12
Experts,
I am attempting to do a Dlookup.  I have a syntax.  I think the issue is somewhere around the "179" part but I dont really know.  

If the Guarantor Description is like "DNB*" then I need to lookup the FacilityAmount in tblBankFacilityAmts then subtract the SUM [Approved Amount In USD Equivalent]

The [Approved Amount In USD Equivalent] is a field in the query.  circled in the pic.

here it is:
Available: iif("[Guarantor Description]" like "DNB*", Dlookup("[FacilityAmount]","tblBankFacilityAmts", "[BankName_Facility] =" 179 – Sum([Approved Amount In USD Equivalent],0) Qry Design Grid
0
Comment
Question by:pdvsa
  • 5
  • 4
  • 4
  • +1
16 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37051070
Try this -


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

Expert Comment

by:Norie
ID: 37051071
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)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37051077
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)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75
ID: 37051082
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
0
 

Author Comment

by:pdvsa
ID: 37051093
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))

0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 37051097
IIf([Guarantor Description] Like "DNB*",DLookUp("[FacilityAmount]","tblBankFacilityAmts","[BankName_Facility] =179")-Sum([Approved Amount In USD Equivalent]))
0
 
LVL 75
ID: 37051100
OK this:

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

mx
0
 
LVL 35

Expert Comment

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

Author Comment

by:pdvsa
ID: 37051117
Imnorie:  that was the solution.

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

?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37051157
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.
0
 

Author Comment

by:pdvsa
ID: 37051164
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.
0
 
LVL 35

Expert Comment

by:Norie
ID: 37051166
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)
0
 
LVL 75
ID: 37051169
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
0
 
LVL 35

Expert Comment

by:Norie
ID: 37051187
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37051188
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).
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question