Dlookup Within Dsum Query (1 Viewer)

L

Laser

Registered User.

Local time

Today, 10:27

Joined

Nov 18, 2009

Messages

17

• Aug 20, 2019

• #1

I dont know if it's me or what, but I always have problems with the correct syntax for the D's... You know the Dlookup, DSum,DLast and so on but I generally find the answer using Google and various forums to glean an answer. But this one has me flumoxed. My question is can I use a Dlookup within a Dsum calculation for a Textbox? If so, can any kind person indicate what I am doing wrong with the following line of code?


=DSum("AmountPaid","InvoicePayments","ChequeNumber <>'Credit' AND PaymentDate = #" & DLookUp("[Completed]","AncillaryData") & "#")


Many thanks in anticipation..

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 17:27

Joined

May 7, 2009

Messages

19,230

• Aug 20, 2019

• #2

you are only dlookup-ing the [completed] field of ancillarydata.
this will return the first record it will find in that table.
you must add a Criteria.

 

D

Dreamweaver

Well-known member

Local time

Today, 10:27

Joined

Nov 28, 2005

Messages

2,466

• Aug 20, 2019

• #3

Why not use a total query then you would only need to lookup the value for the date



mhope it helps mick

 

L

Laser

Registered User.

Local time

Today, 10:27

Joined

Nov 18, 2009

Messages

17

• Aug 20, 2019

• #4

Hi arnelgp, Thanks for looking and responding to my problem. Ancillarydata holds various data under different names so no need for criteria.

DLookUp("[Completed]","AncillaryData") works fine as standalone and so does the other part DSum("AmountPaid","InvoicePayments","ChequeNumber <>'Credit' AND PaymentDate= #16/8/2019#")

But together they fail to produce a result so I guess its something to do with the date being pulled from AncillaryData and inserted into the whole line of code.

 

isladogs

MVP / VIP

Local time

Today, 10:27

Joined

Jan 14, 2017

Messages

18,213

• Aug 20, 2019

• #5

Using Domain functions in a query is slow as each record has to be checked row by agonising Row.
To compound that using both DSum and DLookup will give poor performance if you get it working. Suggest trying a different approach

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 17:27

Joined

May 7, 2009

Messages

19,230

• Aug 20, 2019

• #6

as I have said it will pick up the first record in dlookup.
if only 'Credit' is on the table on that date, it will return Null.
so better cast the expression to Nz() function.

Code:Copy to clipboard

=Nz(DSum("AmountPaid", "InvoicePayment", "ChequeNumber <>'Credit' AND PaymentDate = #" & DLookup("[completed]", "AncillaryDAta") & "#"), 0)