asked on

Dlookup top record with two criteria

Hi there,

I have a table called tblUserChanges. Within this table there are the following fields:
[Change] - Text
[ChangeDate] - DateTime
[Actioned] - YesNo
[UserID] - Number

I would like to get the latest [Change] value (using [ChangeDate]), where [Actioned] = True and [UserID]= Me.UserID

Please help! Cheers, LoveToSpod.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

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

Start Free Trial

LoveToSpod

ASKER

Patrick

I'm getting the following error in the MaxDate line: "DataType mismatch in criteria expression"

Sham Haque

as userid is number, leave out the additional quotes:

MaxDate = DMax("[ChangeDate]", "[tblUserChanges]", "[Actioned] = True AND [UserID] = " & Me.UserID & )

SOLUTION

Sham Haque

membership

This solution is only available to members.

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

Start Free Trial

peter57r

Is MaxDate Dim-ed as a date variable?

If you are using UK dates try:
MsgBox DLookup("[Change]", "[tblUserChanges]", "[ChangeDate] = #" & format(MaxDate,"yyyy-mm-dd") & "# AND [UserID] = '" & Me.UserID & "'")

SOLUTION

peter57r

membership

This solution is only available to members.

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

Start Free Trial

Sham Haque

peter - hehehe - and I assume patrick's popped out for lunch (and maybe a cheeky pint!)

GRayL

Try making this the control source for a textbox on the form on which you have the textbox - UserID

=DLookup("Change",'tblUserChanges","ChangeDate=DMax("ChangeDate", "tblUserChanges", "Actioned = True AND UserID = " & Me.UserID & ") AND Actioned = True AND UserID = " & Me.UserID & """)

SOLUTION

GRayL

membership

This solution is only available to members.

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

Start Free Trial

GRayL

loopup - lookup