DLookup Syntax

[sanderson]

Hello:



I would like to populate the ControlSource for a form field using DLookup. I have this, but it is not working...

=DLookUp(Sum([TimeCards]![laborhours]),[TimeCards],[TimeCards]![Date]=[Forms]![frmTimeCard]![txtDate] And [TimeCards]![empid]=[Forms]![frmTimeCard]![txtEmpID])

Thanks in advance!

[June7]

Using Sum() like that is nonsensical. And the DLookup must have literal argument parameters in quote marks and arguments must provide field and table names to search in. A WHERE CONDITION argument is optional.

Maybe you really want DSum.

=DSum("[laborhours]", "[TimeCards]", "[Date]=#" & [txtDate] & "# And [empid]=" & [txtEmpID])

Research use of domain aggregate functions (DLookup, DSum, DAvg, etc). Try Access Help or web. Here is one http://rogersaccessblog.blogspot.com...mystified.html

Date is a reserved word. Should avoid reserved words as names.

[sanderson]

Yes, DSUM is good.

This seems logical (shorter version produced the same). but still resulting in #Error
=DSum("[laborhours]","[TimeCards]"," [TimeCards]![Date] =#" & [Forms]![frmTimeCard]![txtDate] & "# And [TimeCards]![empid] =" & [Forms]![frmTimeCard]![txtEmpID])

I tried this as well...
=DSum("[laborhours]","[TimeCards]"," [TimeCards]![Date] =#" & [Forms]![frmTimeCard]![txtDate] & "# And [TimeCards]![empid] ='" & [Forms]![frmTimeCard]![txtEmpID]) & "'"

And this, which produced not the #Error in the field, but blank data:

=DSum("[laborhours]","[TimeCards]"," [TimeCards]![Date] =" & [Forms]![frmTimeCard]![txtDate] & " And [TimeCards]![empid] =" & [Forms]![frmTimeCard]![txtEmpID])

Thanks for the help. I will read your link now.

[sanderson]

To me, this seems to be the logical choice. By the way, I swapped the # for % because it is a linked SQL table. But it now produces #NAME in the result. #NAME, #ERROR, blank - what's the difference?

=DSum("[laborhours]","[TimeCards]","[Forms]![frmTimeCard].[Date]=%" & [txtDate] & "% And [Forms]![frmTimeCard].[empid]='" & [txtEmpID] & "'")

Thanks again...

[sanderson]

So I figured it out...

=DSum("[laborhours]","[TimeCards]","[Date]=#" & [Forms]![frmTimeCard].[txtDate] & "# And [empid]='" & [Forms]![frmTimeCard].[txtEmpID] & "'")

But I need to alter it to look at different users, as it's giving me the same data for each user.

I will keep you (the forum) posted.

[June7]

That expression should return data specific to each empid.

Who is the user? Is that the empid?

Is empid a text type field?

[sanderson]

empid is the user, and is a text field. I need to check something else.

[sanderson]

I needed to make some changes. Can you tell me why this is not working?

=DLookUp("complete","tblocalApprovalLog","empid = '" & [Forms]![frmTimeCard].[txtEmpID] & "' AND dayDate = '" & [Forms]![frmTimeCard].[txtDate] & '" & AND weekDate = '" & [Forms]![frmTimeCard].[txtPeriodStart] & " - " & [Forms]![frmTimeCard].[txtPeriodEnd] & "' ")

The date fields are strings, not dates in the table.

I am getting an error that says I may have entered an operand without an operator.

Thanks.

[June7]

Misplaced quote mark and & before AND weekDate, should be: & "' AND weekDate =

Don't put the minus sign (-) within quote marks and drop the & on each side - in other words, do the arithmetic so only the result is concatenated.

[sanderson]

Great Catch.

Can you tell me, is there a good way to have the control source be this, and also be able to change the value? Complete is a boolean value tied to a textbox.

Thanks again!

[June7]

Not by user input to the same control. Change what value? Requires code to save calculated result.

[sanderson]

Yes, thanks. I was somehow thinking I could do two things with one control.