Software

Ask something
Discussion Locked

Access 97- Reports grouped by Date

0 helpful
1 reply

by shumie11

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have developed a database in Ms Access 97. I have designed reports with dates which are grouped by week. However, the details and calculations on the reports are not accurate since I want the week to Start on a Monday and end on a Sunday. The system has a default week which starts on a Sunday and ends on a Saturday.

Please can you assist me as to how I can change my reports so that the week starts on a Monday and ends on a Sunday.

1 reply

by bristar

This is a sample of one I use in a query that dates a week from Saturday to Friday. The WK field listed is a variable to show the day of the week of the target date according to Access and the DA is the target Date. Essentially, this selects all files where the dates in the file are within the given parameters above and below the target date. Let me know if this helps!

WHERE
IIf([WK]=2,
([tblPromise].[Date])>=[DA]-0
And ([tblPromise].[Date])<=[DA]+6,
IIf([WK]=3,
([tblPromise].[Date])>=[DA]-1
And ([tblPromise].[Date])<=[DA]+5,
IIf([WK]=4,
([tblPromise].[Date])>=[DA]-2
And ([tblPromise].[Date])<=[DA]+4,
IIf([WK]=5,
([tblPromise].[Date])>=[DA]-3
And ([tblPromise].[Date])<=[DA]+3,
IIf([WK]=6,
([tblPromise].[Date])>=[DA]-4
And ([tblPromise].[Date])<=[DA]+2,
IIf([WK]=7,
([tblPromise].[Date])>=[DA]-5
And ([tblPromise].[Date])<=[DA]+1,
IIf([WK]=1,
([tblPromise].[Date])>=[DA]-6
And ([tblPromise].[Date])<=[DA]+0)))))))

This discussion is closed to new replies.