Source control on form DLookup not working

[NanR]

Hi,
I'm using the DLookup function in a field to provide the results of a calculation field from a qry.

=(DLookUp("[qryTotalBalance]![TotalRecAmt]","qryTotalBalance"))

I set the default value to 0.00 and format to currency.
I only see 0.00 not the field results.
I'm not sure what is wrong.
Any help is appreciated!
Thanks, NanR

[twinnyfo]

NanR,

Welcome to Bytes!

Where is this DLookUp() Function? Is it on a Form? What causes the value to update?

Also your syntax can be shortened:

=DLookUp("[TotalRecAmt]","qryTotalBalance")

[zmbd]

I've stepped this w/o the continuation, so it will not work w/o reformatting:

=(DLookUp("[qryTotalBalance]![TotalRecAmt]",
   "qryTotalBalance",
   Missing Conditional clause))

Allen Browne: Getting a value from a table: DLookup()

You may need the formal references depending on the context; however, TwinnyFo is correct that most of the time the syntax can be shortened.

Also in VBA, I advise building the where clause as a string variable first and then using the string variable in the function.

[twinnyfo]

Z,

Yes--although it is possible to get just the first value, but one should always include the conditional clause to find the "right" value.

[NanR]

Hi,
My DLookup field is on a form. I have two tables; tblOverdrawnAccts and tblRecoveryAcctGLs. I need totals for the overdrawn accts tbl and totals for recovery accts tbl.
I wrote a query to obtain these totals. Then I added a calculated field in the query to obtain the difference of these two totals. I am using the DLookup field on my form from the query fields. SQL of my query:

SELECT tblOverdrawnAccts.CustID
   , tblOverdrawnAccts.TotalODAmt
   , tblRecoveryAcctGLs.TotalRecAmt
   , [tblOverdrawnAccts]![TotalODAmt]
       -[tblRecoveryAcctGLs]![TotalRecAmt] 
         AS NetBalance
FROM tblOverdrawnAccts 
      INNER JOIN tblRecoveryAcctGLs 
         ON tblOverdrawnAccts.AcctID 
            = tblRecoveryAcctGLs.AcctID;

I didn't think that I needed to add the conditional clause. If I do, I'm not sure how I would write it?
Thanks,NanR

[NanR]

I think I've got it. I used the following and added the conditional clause...

=(DLookUp("[qryTotalBalance]![NetBalance]","[qryTotalBalance]","[CustID]= " & Nz([CustID],0)))

Thank you!!