Dmax of Date

[syedadnan]

Regards,

I am stuck here with a problem that i need a dlookup value of field "A_bal" with respect to bank id "BID" and max of date field "Tdate".

The query name is TNS

an image is attacched

 

[Ranman256]

make 2 queries.
Q1 is: select ID, MAX(date) from tabl (now you know the max date of each ID)

now get the balance...
Q2 uses Q1 and tabl , then the ID in Q1 to pull the balance, join the 2 on ID.

 

[jdraw]

DLookup

Something along this lineuntested)
DesiredValue = DLookup("a_bal","yourtableOrQueryName","aBID=" & txtBID & " and TDate =#" &TDate & "#")

Need more context for the issue to do a test.

 

[syedadnan]

DLookup

Something along this lineuntested)
DesiredValue = DLookup("a_bal","yourtableOrQueryName","aBID=" & txtBID & " and TDate =#" &TDate & "#")

Need more context for the issue to do a test.

Not working.. as for what i need..

 

[syedadnan]

make 2 queries.
Q1 is: select ID, MAX(date) from tabl (now you know the max date of each ID)

now get the balance...
Q2 uses Q1 and tabl , then the ID in Q1 to pull the balance, join the 2 on ID.

Not working.. i think something with dlookup and dmax nest will work.. waiting for any other idea

 

[arnelgp]

SELECT DISTINCT T1.BID, CDATE(DMax("TDATE","yourTABLE","BID=" & T1.BID)) AS TDATE, CDbl(DLookUp("A_BAL","yourTABLE","BID=" & [T1].[BID] & " AND TDATE=#" & DMax("TDATE","yourTABLE","BID=" & [T1].[BID]) & "#")) AS A_BAL
FROM yourTABLE AS T1;

 

[syedadnan]

SELECT DISTINCT T1.BID, CDATE(DMax("TDATE","yourTABLE","BID=" & T1.BID)) AS TDATE, CDbl(DLookUp("A_BAL","yourTABLE","BID=" & [T1].[BID] & " AND TDATE=#" & DMax("TDATE","yourTABLE","BID=" & [T1].[BID]) & "#")) AS A_BAL
FROM yourTABLE AS T1;

WOW !! Thats great cent percent to the requirement... Thanks a billion have a nice day...