asked on

DLookup Statement w/ Multiple Criteria: Error

Hello ~ I am intending to find the value of "ttSitus" from the table "tblTimeTracking" where BOTH the "ttPerson" = "ttPerson" in the record on the form AND the largest "ttID" for that same "ttPerson".

Right now, the following statement is retrieving the "ttSitus" with the largest "ttID"; regardless of "ttPerson".

Any ideas for correct syntax?

=DLookUp("[ttSitus]","tblTimeTracking","[ttPerson] = forms!frmTimeTracking!ttPerson and ([ttID]=dmax(forms!frmTimeTracking!ttID))")

also tried:

=DLookUp("[ttSitus]","tblTimeTracking","[ttPerson] =" & [Forms]![frmTimeTracking]![ttPerson] And [ttID]=Max([ttID]))

also tried:

=DLookUp("[ttSitus]","tblTimeTracking","[ttPerson] =" & [Forms]![frmTimeTracking]![ttPerson] And "[ttID]=" & Max(DLookUp("[ttID]","tblTimeTracking","[ttID]")))

Definitely missing something here...
To dreamtime; WCB tomorrow.

Many Thanks ~ Jacob

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chi Is Current

ASKER

Thank you, Gustav, for your reply!!!

Person IS a string; however, this code does not resolve:
"Invalid syntax" - missing operator or operand or invalid character or comma or text without surrounding quotation marks. ???

Chi Is Current

ASKER

OK, a '[' is needed, on ttPerson], second line ...

But still not there = expression too long ????

Chi Is Current

ASKER

OK + another ":

=DLookUp("[ttSitus]","tblTimeTracking","[ttPerson] = '" & Forms!frmTimeTracking!ttPerson & "' And [ttID] = " & DMax("[ttID]","tblTimeTracking", "[ttPerson] = '" & Forms!frmTimeTracking!ttPerson & "'") & "")

DOES RESOLVE, but not catching the last ttSitus for ttPerson w/ Dmax ttID...
???

Chi Is Current

ASKER

Hmmmmmmm......

This does work, sort of. It catches a previous value of ttSitus, not necessarily the last one for ttPerson...

BTW I am using this function as the Default property of a control on a form.

Chi Is Current

ASKER

OOOOOOOOOOOOOOOOK!!!!! Works just fine!!! I think the problem is using the function as the default value. Gets set prior to the field's update......

I'll but into an Event...

'Should have gone to bed......

Thank you for the beautiful code and the lesson.

Best Regards, Jacob

Chi Is Current

ASKER

P.S. ~ That did it. On AfterUpdate for another control!!!!

THANK YOU!!!!

Gustav Brock

You are welcome!

/gustav

Chi Is Current

ASKER

/gustav ~

Follow-up posted here:
https://www.experts-exchange.com/questions/28132114/DLookup-Syntax-DATE-Multiple-criteria.html

Revising same code to work with a DATE field.
Results in null.

Care to comment?

Best Regards, Jacob