By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,189 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

MS Access: Calculate date interval between records in a table

P: 1
I'm beginner and getting following error message, please help.
The table contains transaction date, account number, branch, etc. And need to show previous transaction date or next transaction date of the account number next to each record,
then calculate date interval.
Thank you in advance,
Sean,
----error message----
"You tried to execute a query that does not include the specified expression 'NEXTDATE' as part of an aggregate function.
----access query----
SELECT A.TRDAT7, B.TRDAT7 AS NEXTDATE, DateDiff("d",A.TRDAT7,NEXTDATE) AS dateinterval, A.BRANCH, A.TRACCT
FROM LNREDFLAG AS A LEFT JOIN LNREDFLAG AS B ON (A.TRACCT=B.TRACCT) AND (A.TRDAT7<B.TRDAT7)
GROUP BY A.TRDAT7, A.BRANCH, A.TRACCT;
Nov 14 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 374
I'm beginner and getting following error message, please help.
The table contains transaction date, account number, branch, etc. And need to show previous transaction date or next transaction date of the account number next to each record,
then calculate date interval.
Thank you in advance,
Sean,
----error message----
"You tried to execute a query that does not include the specified expression 'NEXTDATE' as part of an aggregate function.
----access query----
SELECT A.TRDAT7, B.TRDAT7 AS NEXTDATE, DateDiff("d",A.TRDAT7,NEXTDATE) AS dateinterval, A.BRANCH, A.TRACCT
FROM LNREDFLAG AS A LEFT JOIN LNREDFLAG AS B ON (A.TRACCT=B.TRACCT) AND (A.TRDAT7<B.TRDAT7)
GROUP BY A.TRDAT7, A.BRANCH, A.TRACCT;
As long as the dates are different, then you would not use NextDate in your DateDiff Function, you would simply place B.TRDAT7 in place of NEXTDATE

Let me know if that workes.

Thanks,

Joe P.
Nov 17 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.