Calculating and reporting dates

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

I need a report that will display the date of each Saturday between two
dates that are entered into two (unbound) controls on a form (they are
called 'datefrom' and 'dateto'). Each Saturday date needs to be on a new
line - as if each one was in fact a record in a table. The only way I can
think of doing this is to add a record to a table for each of the Saturdays,
but that seems longwinded and just plain wrong!

Hope someone can help.

Many thanks in advance
Leslie Isaacs
 
The first Saturday in the range would be:
[datefrom] - Weekday([datefrom]) + 7

You will need a counting table, with an integer field from 0 to the maximum
number of weeks you could ever need betweeen the 2 values. There's a
description of how to build this table, and some code to populate it (so you
don't have to type the numbers manually) in this link:
http://allenbrowne.com/ser-39.html

Now create a query using tblCount.
Type this into the Field row:
TheSaturday: [datefrom] - Weekday([datefrom]) + 7 * tblCount.CountID
In the Criteria row under this field, enter:
Between [datefrom] And [dateto]

To ensure Access understands this correctly, it would be a really good idea
to explicitly declare your 2 parameters. Choose Parameters on the Query
menu. Access opens a dialog. Enter 2 rows:
[datefrom] Date/Time
[dateto] Date/Time
 
Leslie said:
I need a report that will display the date of each Saturday between two
dates that are entered into two (unbound) controls on a form (they are
called 'datefrom' and 'dateto'). Each Saturday date needs to be on a new
line - as if each one was in fact a record in a table. The only way I can
think of doing this is to add a record to a table for each of the Saturdays,
but that seems longwinded and just plain wrong!
Click to expand...


You can use a general purpose utility table instead of a
special purpose saturdays table. Create a table (named
Numers) with one field (named Num) and populate it with
records containing 1,2,3,... up to more than the greatest
number of weeks you will ever need.

Then you can use a query like this to "create" the records:

SELECT DateAdd("d", 7*(Num-1), Forms!yourform.txtDateFrom)
As Sat
FROM Numbers
WHERE DateAdd("d", 7*(Num-1), Forms!yourform.txtDateFrom)
<= Forms!yourform.txtDateTo

I suspect there is more to your question than just a report
of saturday dates, but I can imagine too many scenarios to
guess what you might need without more information.
 
Hello Allen

Many thanks for your suggestion, which I now have working perfectly.
Initially there seemed to be a problem getting the query to take the
datefrom and dateto values from the textboxes on the form (called
[frmDateRange]), but I've now got it to work. The query I have is:

SELECT
CDate([forms]![frmDateRange]![datefrom]-Weekday([forms]![frmDateRange]![datefrom])+7*[tblCount].[CountID])
AS TheSaturday
FROM tblCount
WHERE
(((CDate([forms]![frmDateRange]![datefrom]-Weekday([forms]![frmDateRange]![datefrom])+7*[tblCount].[CountID]))
Between [forms]![frmDateRange]![datefrom] And
[forms]![frmDateRange]![dateto]));

Did I need to do this?

Many thanks again
Les



Allen Browne said:
The first Saturday in the range would be:
[datefrom] - Weekday([datefrom]) + 7

You will need a counting table, with an integer field from 0 to the
maximum number of weeks you could ever need betweeen the 2 values. There's
a description of how to build this table, and some code to populate it (so
you don't have to type the numbers manually) in this link:
http://allenbrowne.com/ser-39.html

Now create a query using tblCount.
Type this into the Field row:
TheSaturday: [datefrom] - Weekday([datefrom]) + 7 * tblCount.CountID
In the Criteria row under this field, enter:
Between [datefrom] And [dateto]

To ensure Access understands this correctly, it would be a really good
idea to explicitly declare your 2 parameters. Choose Parameters on the
Query menu. Access opens a dialog. Enter 2 rows:
[datefrom] Date/Time
[dateto] Date/Time

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Leslie Isaacs said:
Hello All

I need a report that will display the date of each Saturday between two
dates that are entered into two (unbound) controls on a form (they are
called 'datefrom' and 'dateto'). Each Saturday date needs to be on a new
line - as if each one was in fact a record in a table. The only way I can
think of doing this is to add a record to a table for each of the
Saturdays,
but that seems longwinded and just plain wrong!

Hope someone can help.

Many thanks in advance
Leslie Isaacs
Click to expand...
Click to expand...
 
Okay, you have it working.

I'd be inclined to declare the parameters rather than use CDate().
 
Hello Allen

OK - done that!
The problem the first time I tried that was that I didn't give the full
[forms]![frmDateRange]![datefrom] in the parameters dialogue box - I just
gave datefrom: my mistake!

Thanks again
Les

Allen Browne said:
Okay, you have it working.

I'd be inclined to declare the parameters rather than use CDate().

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Leslie Isaacs said:
Many thanks for your suggestion, which I now have working perfectly.
Initially there seemed to be a problem getting the query to take the
datefrom and dateto values from the textboxes on the form (called
[frmDateRange]), but I've now got it to work. The query I have is:

SELECT
CDate([forms]![frmDateRange]![datefrom]-Weekday([forms]![frmDateRange]![datefrom])+7*[tblCount].[CountID])
AS TheSaturday
FROM tblCount
WHERE
(((CDate([forms]![frmDateRange]![datefrom]-Weekday([forms]![frmDateRange]![datefrom])+7*[tblCount].[CountID]))
Between [forms]![frmDateRange]![datefrom] And
[forms]![frmDateRange]![dateto]));

Did I need to do this?
Click to expand...
Click to expand...
 
Hello Marshall

Thanks for your reply. I have used Allen's suggestion, which looks very much
like yours ... and this gets me to the next step: as you guessed, this is
part of a bigger problem - the next part of which I am now struggling with!!

I need the report to show one row for each Saturday date. In each row there
must be 8 columns: one for the Saturday date, and one for a calculated value
corresponding to each of the 7 dates in that week (including the Saturday).
I did try producing the report by basing it on a table with a row for ALL
the dates within the specified range (as opposed to just the Saturday
dates), and specifying 8 columns in page>setup, but that didn't work for
reasons that are probably obvious to you!

The full task is as described in my post today on the Table Design newsgroup
(headed "One table or two"), which I have copied below. I was (and still
am!) concerned that that post seems too long for anyone to get into. In the
light of your and Allen's replies to my post in this Reports newsgroup, I'm
now confused about whether I need a table with a row for each date within
the range, or just a row for each Saturday within the range. If I just go
for the Saturday dates I think the required layout of the report will be
much easier to achieve: but I would then need 7 further columns in the
table, labelled "Sun", "Mon", "Tue", "Wed", Thur", "Fri" and "Sat", to
store the calculated values corresponding to each day.

I hope this makes sense, and I really would be very grateful if you could
let me know your thoughts.

Many thanks once again
Les

My post in the Table Design newsgroup was:

I have to create a facility within an existing 'Employee' database to manage
employee sickness absence. The data to be input will be the employee's name,
and the start and end dates of any sickness absence periods. These dates,
together with some other 'standing' data held in the existing 'employee'
table, will be used to calculate certain values for each day during the
sickness absence period: essentially, each day during the sickness absence
period will have to be annotated as being of one of 3 'types' (denoted as
"W", "S" or "N"). In addition, each day during the sickness absence period
will be assigned by the user to a 'pay period' (there is an existing table
of 'pay periods'). The rule that determines the 'type' will take account of
the length of the absence period, the proximity of other absence periods for
that employee, and certain other data held about the employee in the
'employee' table. Once calculated it is important that the 'type', and the
'pay period', that are assigned to each day during an absence period are
saved so that their values can be retrieved in the future. They cannot
always be re-calculated because some of the data used in the calculations
may change (primarily the data range of the sickness absence itself) - but
the results of the original calculation must be retained.

The main output needs to be a report showing, for a selected employee and
selected date range, every date between the selected date range, with each
date being displayed either with its 'sickness type' etc. if that date IS
during one of the employees absence periods, or with no 'type' etc. if it is
NOT during one of the employees absence periods. The actual layout of the
report must be to show the absence 'type' (in the case of absence dates), or
a blank field (in the case of other dates) for complete weeks horizontally -
e.g. with field names:

"Sun", "Mon", "Tue", "Wed", Thur", "Fri", "Sat", "Week ending"

the report might look like:

W W S S N N 26/07/08
S S N N N N N 02/8/08
07/8/08
14/08/08
N N N N S S N 21/8/08

The first row above would represent one blank representing 20/08/08, two
"W"s representing 21/08/08 and 22/08, two "S"s representing 23/08/08 and
24/08/08 and two "N"s representing 25/08/08 and 26/08/08. The third and
forth rows represent week with 7 'blanks' - i.e. these weeks contain no
dates that fall within any sickness absence period for the employee.
The assigned 'pay period' would not be included on this report.

My initial plan was to have a table 'sickness_periods', containing just the
employee name and the start and end dates of each sickness period, plus a
table 'sickness_days', containing the results of the calculations. Now I am
wondering whether I shouldn't bother with the first 'sickness_periods'
table, and instead just enter the data range into two unbound fields on the
data entry form, do the calculations and then save the results to the
'sickness_days' table.
 
Back
Top