Using VBA to perform action on every field in a table

[irishalanm]

Hi,
I am an ICT Student and I am designing a work related project which is using MS Access 2007 to create a database to monitor leave for fire brigade staff.

I have studied relational database with SQL but am new enough to Access.

The shift patternns follow a 28 day cycle. I have a table with fields containing dates

My question is: how might I, using VBA, or otherwise add 28 days to this table to create a new table of dates, without having to do it manually.

Thanks in advance.

Alan

[NeoPa]

My question is: how might I, using VBA, or otherwise add 28 days to this table to create a new table of dates, without having to do it manually.

Hi Alan. Welcome to Bytes!

I'm not clear what you mean by adding 28 days to a table. I'm sure we can help you (VBA has the power), but we need a clearer understanding first of precisely what you're trying to achieve.

[irishalanm]

Ok Sorry about being less than clear!

What I want to do is advance each date in a table of dates by exactly 28 days. I suppose I would have to set up a loop and for each field value use the AddDate() function to add 28 to each and either append the new values in the existing table or create a new table, leaving the original dates unaffected. Does that explain it any better?

Thanks again

Alan

[NeoPa]

I would do it in SQL, but would need an idea of what the field to be changed is called (when the new record is added), as well as a way of determining which records to copy after there are a number of duplicates in the table.

How about posting the table structure to look at, and explaining, in that scenario, how you would want to identify the records that need to be copied.

[irishalanm]

Hi NeoPa,
Thank you for your time and apologies for the delay in reply. I think I need to give you some background to my project so you can see what I am trying to achieve. I am designing a database to monitor leave (days off) for a company that has different groups of staff on different shifts. Because the employees work more hours than the standard week they are entitled to different leave throughout the year. These are rostered to every employee at different times of the year but they can exchange or swap dates of leave for different dates provided the shift patterns are the same or similar. My main problem is to devise a method to auto-populate tables with dates so that the dates are correct, as mistakes can be made manually, and also because inputting the dates would be too time consuming.

For each of the groups of workers, their shift pattern repeats ad infinitum EVERY 28 DAYS. So if I can find a way that a table could be populated based on one 28 day block by continually adding 28 days thast might solve my problem.

Also I need to refer to a calender so that I can cross reference what day of the week a particular date is.

I have been trying to find out if I can get 'under the bonnet' of the calender control tool in MS Access 2007 but so far all I have been able to find is a way to add a calender date picker to a form or use the date picker to pick individual dates on a table.

I have seen comments on importing outlook calender from outlook using VBA?

Any other ideas?

Regards

Alan

[NeoPa]

I'm starting to get a picture, but see various potential problems. I need a better understanding I expect.

What sort of data are you storing for a 28-day block? Some example data would help greatly.

Clearly, advancing by 28 days avoids any weekday issues, but holidays may be lost from one to the next. Possibly a template would be preferable. I still need a clearer understanding to advance any.

[NeoPa]

Consider also, any block of 28 days will be interesting within that block, as well as for a certain period before and after it. Having only one set of the data in the table at any one time is likely to be too limited.

[irishalanm]

I am storing several tables with nothing but dates in them and I need to have a calender in a table as well. I plan to cross reference dates. I have seen previous thread where ADezii posted an example database from Philly Fire Dept a while back but the link has been removed so i couldnt see it but it sounds like it could be similar to what I want to achieve.

Regards

Alan

[Dan2kx]

Could you store this shift information as a day number, eg

StafffID 	DayNo	Shift (hrs?)
1	1	7.5
1	2	7.5
1	3	8.5
1	4	9
…		
2	1	7.5
2	2	7

then use a Mod 28 function on said dates to pick out patterns on the fly, storing leave dates elsewhere if needed?

Dan

[NeoPa]

How about a set of template data. This could be stored in a separate table.

If the results you're after is simply a set of dates (no other fields as indicated in post #8), then the template could be a set of records storing date offsets only. That way, on being passed a base date, you could easily create a set of dated records which match the offsets stored in the template table, beyond the base date given.

This could be called whenever a new block of dates is required.

Does this sound like a viable solution?

[irishalanm]

The problem is I don't know how to do it. Do I use VBA with the DateAdd() function? Does anyone know how to access the calendar control function in Access 2007 or is this possible?
Thanks

Alan

[irishalanm]

Hi Dan
The tables contain dates and an important part of the project is to cross reference these dates. I was thinking about using DateAdd() function but I don't know how I would access the table in VBA or otherwise.

Thanks

Alan

[Dan2kx]

If i understand the problem correctly then you dont need to store and update dates,you can use the template to store shift patterns and then refer to that as a cycle of 28 days. you can then in another table if you need to store specific dates for someone to be "off". and with regard to ADezii's calendar you easily adapt it to show information from this template table.

[NeoPa]

The problem is I don't know how to do it. Do I use VBA with the DateAdd() function? Does anyone know how to access the calendar control function in Access 2007 or is this possible?
Thanks

Alan

If the template held offset values as suggested, you could create some SQL to use DateAdd() with the offset field value as the offset and a reference to the date on your form somewhere as the base date to which the offset is applied.

IE. Records with offset values of 1, 2 & 5 and a base date (on your form) of 20 April 2009 would produce date records in your destination table of :
21 April 2009, 22 April 2009 & 26 April 2009 respectively.

Does that make the concept easier to follow?

[irishalanm]

ADezii's calendar

Yes sounds good-could someone attach ADezii's calendar? I'm afraid the attachment is no longer there from thread way back.

Thanks

Alan

If i understand the problem correctly then you dont need to store and update dates,you can use the template to store shift patterns and then refer to that as a cycle of 28 days. you can then in another table if you need to store specific dates for someone to be "off". and with regard to ADezii's calendar you easily adapt it to show information from this template table.