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:
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
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:
Date | Points | % |
8/1/2021 | 100 | |
8/2/2021 | 50 | 50% (% of 8/2 points against 8/1 points) |
8/5/2021 | 75 | 150% (% of 8/5 points against 8/2 points) |
8/6/2021 | 32 | 42.67% (% of 32 vs 75) |
8/9/2021 | 280 | 875% (% 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