Question Creating date

[djchapple]

I wish to create a a list of dates in a database.

I have a database which, amongst other things, holds the list of people who have volunteered to man the Visitor Centre at my local Nature Reserve. This section of the database is effectively just a calendar.

The first column holds the ID which is automatically generated with each new record.

Using this ID I would like to print out a report containing the full date in a particular format.

I thought of using the DateAdd Expression in a form similar to that below.

=DateAdd("d",+[ID],"30-Dec-05")

where 30-Dec-05 is the day before the rota started.

1. Would this expression calculate the date from the ID?

2. Would I use this or a corrected expression in a Query?

3. How can I ensure that the report based on this query would output the dates in correct order?

I would like to print out the date as

Nov 2009 Sat 31

Could I use the following format?

mmm," ",yyyy," ",ddd," ",d

and if so where?

 

[ColinEssex]

There are only 30 days in November.

Col

 

[Poppa Smurf]

I have a problem with you using =DateAdd("d",+[ID],"30-Dec-05"). If you delete a record then your dates are incorrect.

How are you recording the dates for the Volunteers? Is each new record for one volunteer, one record for many volunteers?

I would use some code to populate the record source of a combo box with the dates say for the next three months etc. and use this date selected to record the availability of your volunteers. This would ensure the dates are in the correct order.

 

[djchapple]

Re November 31st - just testing to see if those of you at the back are still awake!!!

A record inn the database will have the following columns (plus many more)

1. ID (Record ID and day number - to be used to calculate the date shown in the report.
3. Special info (i.e Christmas Day, Fun day)
4. Morning (contains names of volunteers on duty)
3. Afternoon (contains then names of volunteers on duty)

The actual date shown in the report = start date + X days, where X is an integer numerically equal to the ID.

Alternatively I could generate a date in the database, unfortunately I have no idea whether this is possible or not.

 

[Poppa Smurf]

Depending on your report layout

I would have the following
1. A table to store the dates with the following fields
volunteer_date - the date to record volunteer's availability
special_info - Christmas, Fun Day etc.
2. A table for the volunteers' availability with the following field
volunteer_date - the date to record volunteer's availability
special_info - Christmas, Fun Day etc.
volunteer_period - Morning, Afternoon, etc.
volunteer_name - Name of the volunteer
There would be one record for each volunteer.

The above will give you a report layout similar to

Date Period Volunteer
23/10/2009

AM

J. Brown
M. Smith
​

23/10/2009

PM

J. Brown
M. Jackson
​

Code to create the dates requires just a few lines of code, i can provide this if required.

If you require assistance please reply to me off list.