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.
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.
Microsoft Access
Last Comment
GRayL
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
as userid is number, leave out the additional quotes:
MaxDate = DMax("[ChangeDate]", "[tblUserChanges]", "[Actioned] = True AND [UserID] = " & Me.UserID & )
MaxDate = DMax("[ChangeDate]", "[tblUserChanges]", "[Actioned] = True AND [UserID] = " & Me.UserID & )
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
If you are using UK dates try:
MsgBox DLookup("[Change]", "[tblUserChanges]", "[ChangeDate] = #" & format(MaxDate,"yyyy-mm-dd
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
peter - hehehe - and I assume patrick's popped out for lunch (and maybe a cheeky pint!)
Try making this the control source for a textbox on the form on which you have the textbox - UserID
=DLookup("Change",'tblUser
=DLookup("Change",'tblUser
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
loopup - lookup
I'm getting the following error in the MaxDate line: "DataType mismatch in criteria expression"