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
LVL 2
Chi Is CurrentAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
First the max:

DMax("[ttID]","tblTimeTracking",ttPerson] = " & Forms!frmTimeTracking!ttPerson & "")

Then:

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

or, if Person is a string:

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

/gustav
0
 
Chi Is CurrentAuthor Commented:
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.   ???
0
 
Chi Is CurrentAuthor Commented:
OK, a '[' is needed, on ttPerson], second line ...

But still not there = expression too long  ????
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Chi Is CurrentAuthor Commented:
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...
???
0
 
Chi Is CurrentAuthor Commented:
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.
0
 
Chi Is CurrentAuthor Commented:
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
0
 
Chi Is CurrentAuthor Commented:
P.S. ~ That did it.  On AfterUpdate for another control!!!!

THANK YOU!!!!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
Chi Is CurrentAuthor Commented:
/gustav ~

Follow-up posted here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28132114.html

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

Care to comment?

Best Regards,  Jacob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.