Solved DLookup in Query for a Previous Entry (1 Viewer)

jwbird46342

New member
Local time
Today, 08:18
Joined
Aug 24, 2021
Messages
2
I'm very new to AccessWorld (actually my 1st post) so please forgive me if I'm not doing something exactly right in the forum.

I'm keeping track of points by date and I'd like to have a query calculate the percentage as compared to the previous days (or previous entry's) points:

DatePoints%
8/1/2021100
8/2/20215050% (% of 8/2 points against 8/1 points)
8/5/202175150% (% of 8/5 points against 8/2 points)
8/6/20213242.67% (% of 32 vs 75)
8/9/2021280875% (% of 280 vs 32)

My issue is I'm trying to use a DLookup function to return a value from a previous date's entry but clearly don't have it right since I'm getting nothing back or "Data type mismatch". I've tried the following code but am getting the corresponding errors:

PrevPts: DLookUp("Points","tblProgress","PtsDate= '[PtsDate]-1'") "Date Type mismatch"
PrevPts: DLookUp("Points","tblProgress","PtsDate= #'[PtsDate]-1'#") "Syntax error in date in query expression...."
PrevPts: DLookUp("Points","tblProgress","PtsDate= #'lookupdate'#") "Lookupdate" is a separate calculation in the same query that calculates PtsDate-1" "Syntax error in date in query expression...."

Any help would be greatly appreciated!!

Thx
 

Ranman256

Well-known member
Local time
Today, 09:18
Joined
Apr 9, 2015
Messages
4,339
use date math functions
=DateAdd("d", -1, [dateFld])
 

jwbird46342

New member
Local time
Today, 08:18
Joined
Aug 24, 2021
Messages
2
use date math functions
=DateAdd("d", -1, [dateFld])
Using (PrevPts: DLookUp("Points","tblProgress","DateAdd('d', -1, [PtsDate])") kind of worked in that it's returning a number but it's the 1st record's points for every entry.
 

Attachments

  • Capture.JPG
    Capture.JPG
    24.9 KB · Views: 370

plog

Banishment Pending
Local time
Today, 08:18
Joined
May 11, 2011
Messages
11,611
The correct way to do this is with a sub query to find the prior record's date for each record in tblProgress. Then to make it more complicated that sub query will use a special subquery itself called a correlated subquery:


Essentially its an inner query that uses the outer query's data as criteria. It can't be done just thru the query designer, it requires actually writing SQL in Access. I did it for you and here's that correlated subquery which determines the prior date for each record:

Code:
SELECT mainq.PtsDate, mainq.Points, (SELECT MAX(PtsDate) FROM tblProgress WHERE tblProgress.PtsDate < mainq.PtsDate) AS PriorDate
FROM tblProgress AS mainq;

PAste that into a new query object, save it and call it 'sub1'. Then use it in another query to get the final results you want using this SQL:

Code:
SELECT sub1.PtsDate, sub1.Points, [sub1].[Points]/[tblProgress].[Points] AS PtsChange
FROM sub1 LEFT JOIN tblProgress ON sub1.PriorDate = tblProgress.PtsDate;
 

Users who are viewing this thread

Top Bottom