Dlookup w/multiple criterias on a subform

[nbrock2002]

Hello, I've hunted and have tested multiple possible solutions without a working one and I need help. I'm trying to use Dlookup in the Expression Builder on subform TimesheetReviewByDateSubform to show PiecesPerHr from EmployeeProductionQuery. The form needs to be edible and the info I'm trying to reference would prevent that so Dlookup has been my solution. If there is another way I'm open to that as well. Here is the formula as it stands now.

=DLookUp("[PiecesPerHr]","EmployeeProductionQuery","[EmployeeProductionQuery].[CustomerDate]=" & [Forms].[TimesheetReviewByDate].[TimesheetReviewByDateSubform].[Form].[CustomerDate] & " And [EmployeeProductionQuery].[EmployeeNumber] =" & [Forms].[TimesheetReviewByDate].[TimesheetReviewByDateSubform].[Form].[EmployeeNumber])

[CJ_London]

Not clear what your question is.

The alternative is to use vba to assign your dlookup value to the control, and your control needs to be bound to the appropriate field, not the dlookup. code might be something like

PiecesPerHr=DLookUp("[PiecesPerHr]","EmployeeProductionQuery","[EmployeeProductionQuery].[CustomerDate]=" & [Forms].[TimesheetReviewByDate].[TimesheetReviewByDateSubform].[Form].[CustomerDate] & " And [EmployeeProductionQuery].[EmployeeNumber] =" & [Forms].[TimesheetReviewByDate].[TimesheetReviewByDateSubform].[Form].[EmployeeNumber])

you don't need to refer to the query each time, so to simplify

PiecesPerHr=DLookUp("[PiecesPerHr]","EmployeeProductionQuery","[CustomerDate]=" & [Forms].[TimesheetReviewByDate].[TimesheetReviewByDateSubform].[Form].[CustomerDate] & " And [EmployeeNumber] =" & [Forms].[TimesheetReviewByDate].[TimesheetReviewByDateSubform].[Form].[EmployeeNumber])

and if your code is in the subform then you can simplify further

PiecesPerHr=DLookUp("[PiecesPerHr]","EmployeeProductionQuery","[CustomerDate]=" & CustomerDate & " And [EmployeeNumber] =" & EmployeeNumber)

or if in the main form

PiecesPerHr=DLookUp("[PiecesPerHr]","EmployeeProductionQuery","[CustomerDate]=" & TimesheetReviewByDateSubform.Form.CustomerDate & " And [EmployeeNumber] =" & TimesheetReviewByDateSubform.Form.EmployeeNumber)

as to which event you run the code on - that depends on what your subform looks like. On the basis it is a single form, then the subform current event would appear to be appropriate. Or perhaps the after update event of the employeenumber control?

[nbrock2002]

I'm still learning but I was using the formula in a Text Box itself. That does not sound like what your suggesting. My subform is a continuous form and my main form is Single.

[pbaldy]

Note the delimiters required for date fields (& text if appropriate):

http://www.theaccessweb.com/general/gen0018.htm

[nbrock2002]

It must have to do with my fields and formats not the formula itself. I get an error #Name?. Will need do some digging. Thanks for the help.

[pbaldy]

Did you add delimiters for what looks like a date/time field?

[nbrock2002]

I did try adding delimiters however Its not really a date. It is a combo of a CustomerNumber and Date which could be part of my problem. I did try to simplify the formula to focus on one criteria at a time but that would not work for me either. I tried both formulas below and am still getting #Name?.

=DLookUp("[PiecesPerHr]","EmployeeProductionQuery","[EmployeeNumber] =" & [Forms].[TimesheetReviewByDate].[TimesheetReviewByDateSubform].[Form].[EmployeeNumber]) &

=DLookUp("[PiecesPerHr]","EmployeeProductionQuery","[EmployeeNumber] ='" & [Forms].[TimesheetReviewByDate].[TimesheetReviewByDateSubform].[Form].[EmployeeNumber] & "'")

[pbaldy]

Can you attach the db here, with test data?

[nbrock2002]

Unfortunately I am not able to, sorry. I know that makes it very tough to help me at this point.

[accesstos]

I suspect that we have to do with a MVF.

@nbrock2002
Put the expression below in an unbound textbox in the subform and let us know what you see:

=[CustomerDate] & " - " & [EmployeeNumber]

Or that expression in an unbound textbox in the main form:

=[TimesheetReviewByDateSubform]![CustomerDate] & " - " & [TimesheetReviewByDateSubform]![EmployeeNumber]

[nbrock2002]

If I put the first code in the subform I do still get an error #Name?.

[accesstos]

Do you use multivalued fields? (I have never used them)

Give a try to these expressions below:
=[CustomerDate]
=[EmployeeNumber]
=[CustomerDate].[Value]
=[EmployeeNumber].[Value]

[June7]

If you can't provide copy of database with confidential info removed, we probably won't be able to help.