Solved

DLookup Statement w/ Multiple Criteria: Error

Posted on 2013-05-17
9
419 Views
Last Modified: 2013-05-17
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
0
Comment
Question by:Chi Is Current
  • 7
  • 2
9 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39174054
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 39174068
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 39174084
OK, a '[' is needed, on ttPerson], second line ...

But still not there = expression too long  ????
0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 39174093
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Author Comment

by:Chi Is Current
ID: 39174105
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 39174187
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 39174202
P.S. ~ That did it.  On AfterUpdate for another control!!!!

THANK YOU!!!!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39174259
You are welcome!

/gustav
0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 39176753
/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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now