Using a previous question as a base Access 2000 Inserting multiple rows based on a date range.
I also wanted to insert multiple rows into a table according to a date range supplied by a user (eg txtRDateStart & txtRDateEnd). The script was envisaged to be able to also gather a time and text description that would be repeated within each row.
For example:
The user would enter...
Start Date: 13/03/2010
End Date: 17/03/2010
Time: 12:00
Description: "Check Mail"
click 'OK'...
Thereby resulting in 5 new rows added to a table 'tblReminders' into columns RDate, RTime & RDescription such as...
13/03/2010, 12:00, Check mail
14/03/2010, 12:00, Check Mail
15/03/2010, 12:00, Check Mail
16/03/2010, 12:00, Check Mail
17/03/2010, 12:00, Check Mail
Using the code provided by PEB in Access 2000 Inserting multiple rows based on a date range
I ended up with the below that is working however the Date being returnned is always the '30/12/1899'.
I understand this is probably due to the way dates are configured in Access and the use of 'Int' in the below code, so I changed to table's RDate field format from short date to nothing and I seem to be getting a time value returning, e.g. for 13/03/2010 the return is 12:03:21 AM. - when I format this to a 'Short Date I again get '30/12/1899'.
Expand|Select|Wrap|Line Numbers
- Dim RepeatEvent
- For RepeatEvent = Int(CDate(Me!txtRDateStart)) To Int(CDate(Me!txtRDateEnd))
- DoCmd.RunSQL "INSERT INTO tblReminder (RDescription, RDate, RTime) " & _
- "SELECT Forms![frmReminder_New]!txtRDescription AS Expr1," & _
- Format$(RepeatEvent, "dd/mm/yyyy") & _
- ", [Forms]![frmReminder_New]![txtRTime] AS Expr3;"
- Next RepeatEvent