By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Access 2000 Inserting multiple rows based on a date range

P: 5
I have a form that records dates of unavailability for a worker.
Based on what is entered in the simple table behind the form, many calculations take place to create employee "load" balance. Each day needs to be inserted as a separate row on the table.
Right now the users enter each date as a separate row.
They want to just be able to enter a range.
How do I add the multiple rows from the date range entered on the form AND exclude weekends?
Oct 10 '06 #1
Share this Question
Share on Google+
4 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi Joanie,

you need to use a VB code to perform this task! Do you want to continue with the code? If yeah, can you tell me the names of your start date and your end date and when do you want that the dates are appended... And how do you see if once the specified range is entered... Maybe it's better to use a single form that it's not based on the form that you mention!

So do you want to proceed with VB code?

Best regards
Oct 14 '06 #2

P: 5
Yes, VB code would be great.
So, here is how I see it working...
They have a form which has a person's name and the dates they will be absent: (begin & end date) October 14th -29th and hours gone per day (because some people will just be absent 4 hours on one day).

The code should insert rows into a table as follows:
John Smith, 10/14/2006, 8 (for 8 hours)
the next row would be
John Smith, 10/15/2006, 8
etc....
THANK YOU!!!!
Hi Joanie,

you need to use a VB code to perform this task! Do you want to continue with the code? If yeah, can you tell me the names of your start date and your end date and when do you want that the dates are appended... And how do you see if once the specified range is entered... Maybe it's better to use a single form that it's not based on the form that you mention!

So do you want to proceed with VB code?

Best regards
Oct 16 '06 #3

P: 5
p.s. and it should exclude weekends.
Oct 16 '06 #4

PEB
Expert 100+
P: 1,418
PEB
Yes, VB code would be great.
So, here is how I see it working...
They have a form which has a person's name and the dates they will be absent: (begin & end date) October 14th -29th and hours gone per day (because some people will just be absent 4 hours on one day).

The code should insert rows into a table as follows:
John Smith, 10/14/2006, 8 (for 8 hours)
the next row would be
John Smith, 10/15/2006, 8
etc....
THANK YOU!!!!
Ok let's do it! :)
Imagine that on your form you have an Ok bouton to that is attached the function. On Click event, you choose event procedure.. Appears a white scrren of VB in which you have to place the following code:
Expand|Select|Wrap|Line Numbers
  1. Sub Ok_onclick()
  2. Dim i
  3.  
  4. For i = int(Cvdate(Me!StartDate)) to int(Cvdate(Me!EndDate))
  5.     if  (DatePart("w", i,vbMonday) <> 6) And (DatePart("w", i,vbMonday) <> 7) then 
  6.       docmd.runsql "INSERT INTO Hours(Worker, Date, Time) VALEUS ('"+Me!WOrker+"',"+Format(i,"dd/mm/yyyy")+",8 );"
  7.   End if
  8. Next i
  9.  
  10. End Sub
The only thing you have to do is to replace The fields and the table name....
Workers, Worker, Date, Time, StartDate End Date with the appropriate ones!

:)
Oct 21 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.