Create records between 2 dates

[S1naps1s]

Is it possible to define 2 dates and create a record for each day in between? I am creating a "sick occurence" database. I want to define the first day the individual calls off sick and the day they will return, then calculate how many sick days they will be using. However, I need to take into consideration any relief days or assigned time off.
Basically, I want to be able to assign what is going on everyday between these 2 defined dates. Be it, holiday, annual leave, personal leave, sick leave, or regular relief. If further clarification is needed, I can go more in depth. Thanks in advance for any help!

[ssanfu]

Yes, you can use VBA to get the start date and end date, then loop to create (insert/append) the required number of records into the table.
Do the math to determine the number of loops required.


And welcome to the forum...

[S1naps1s]

Awesome! Thanks, I wondered if I could do that. Here's my issue, I have tried to do loops before, to no avail. Where can I go to get good info on loops? Or can someone tell me the correct way to do them?

[burrina]

Many things are needed to understand your qualifications and schema. Company Policy, Absence Policy, etc...
Example: Days:

DateDiff("d",[abscenceDtefrom],[AbscenceDteTo])

HTH

[ssanfu]

Someplace you should have two unbound controls to enter the start date and the end date.
In (pseudo) code:

This is a FOR...NEXT loop. There are many types of loops.

  declare variables
  read the value from the start date control
  read the value from the end date control
  subtract the dates (use the DateDiff() function)

'This gives the number of times to loop.

assign  startdate to a variable
For i = 1 to NumDaysToLoop

      INSERT SQL statement - need to add required data EmpID, Start & end date

    increment the date variable by 1 (DateAdd() function)

Next

Where can I go to get good info on loops?

Google "for next loops VBA"

Here is one: http://ms-access-tips.blogspot.com/2...-learning.html

[S1naps1s]

Okay, thanks for the awesome advice, it worked, FLAWLESSLY!!!!!
I do have one additional problem though. Basically all I enter is the start date of the occurrence and the return to work date, from there, VBA does the math for me. I have it immediately put it into the table. So here is my problem, if there is a problem noticed (put in the wrong return to work date or something of the sorts) and the user presses the "calculate" button, it adds a whole new set of records, duplicate though. Is there a way that I can put the data into a "temporary" table and when I press "complete" it puts all the info into the "official" table? That way if I re-compute or cancel the request, there aren't duplicate entries?

[June7]

Using an intermediate 'temp' table won't prevent the duplication. Options:

1. set compound index so Access won't allow duplication and will popup warning to user if they attempt to commit duplicate records

2. code to validate the entries - to check the table for existing records before committing new records

[S1naps1s]

So, I guess I'm thinking on compute onclick, do a dcount if it is greater than 1, then don't allow addition? That's kind of what I'm thinking. Probably the easiest. Any other suggestions?
Now, when I do this, is there a way that I can overwrite rather than not allowing? Almost like, when I copy and paste and there is already a file of the same name there. Can I have the option to overwrite or not allow the change? I'm thinking of how I can best word these questions. It's such an odd problem.

[S1naps1s]

Okay, nevermind, the composite index is DEFINITELY easier. However, now, regarding the ability to overwrite rather than duplicating or being stuck with the results I've previously entered. Can THIS be done?

[June7]

You can run DELETE or UPDATE sql actions.