DLookUp for same dates (1 Viewer)

mfaisal.ce

Member
Local time
Today, 09:05
Joined
Dec 21, 2020
Messages
76
Hello,

I have multiple records with same date in a table and i want to find the last entered record with that specific date... how i can use that with Dlookup function...

DLookup("Balance", "Sales", "CID=" & CID.Value & " and datevalue(Sdate) = #" & DMax("Sdate", "Sales", "CID = " & CID.Value) & "#")

where;
Balance - Field Name
CID- Filed Name
SDate- Field Name

Sales - table Name


kindly guide..
 

June7

AWF VIP
Local time
Yesterday, 22:05
Joined
Mar 9, 2014
Messages
5,399
Don't need to reference Value property.

Did you try that expression - what happens?

Why use DateValue function?

Show sample data and desired output.

Can a CID have multiple records with same date?

Assuming CID is always increasing, perhaps you want the Balance where CID=DMax(CID) AND SDate=DMax(SDate).
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 19, 2013
Messages
16,521
I have multiple records with same date in a table and i want to find the last entered record with that specific date.
dlookup will simply return the first record it finds which matches the criteria - and first in this context is random, almost certainly it won't be the last record you require.

you need something more to be able to identify the last entered record. Perhaps your date field includes a time element? perhaps you have a separate timestamp field? Or you may have to rely on an autonumber field although this is not certain since its purpose is to uniquely identify a record, not provide a sense of order.

The fact you are looking for a balance implies you are storing a calculated value - which in most cases is the wrong thing to do.

perhaps explain what you are actually trying to do. It may be a different method would be more appropriate. Also provide some example data and the outcome required
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 19, 2013
Messages
16,521
guess this is crossposted as well - so I'll drop out and leave this to the other forum
 

Users who are viewing this thread

Top Bottom