Inserting multiple rows based on a Date range

[Vinda]

Hi Bytes,

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'.

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

Any help would be greatly appreciated.

[NeoPa]

Your problem is a lack of delimiters around your date data. It is literal data (rather than a reference) so must be delimited if string or Date/Time (See Literal DateTimes and Their Delimiters (#) and Quotes (') and Double-Quotes (") - Where and When to use them).

It's not clear from this, what type of field [RDate] is in tblReminder. I would guess it's actually a string, but it may conceivably be a Date/Time.

If it's a string then your lines #7 through #9 should be :

             "SELECT Forms![frmReminder_New]!txtRDescription AS Expr1,'" & _
             Format$(RepeatEvent, "dd/mm/yyyy") & _
             "', [Forms]![frmReminder_New]![txtRTime] AS Expr3;"

If it's a date then they should be :

             "SELECT Forms![frmReminder_New]!txtRDescription AS Expr1,#" & _
             Format$(RepeatEvent, "m/d/yyyy") & _
             "#, [Forms]![frmReminder_New]![txtRTime] AS Expr3;"

NB. It doesn't matter where you're from. m/d/yyyy is always correct for SQL literal dates.

[Vinda]

NeoPa that has worked like a charm, thankyou so very much.
My field within tblReminder is a date field and so inserting the '#'s was spot on.
Thanks again, I very much appreciate your time.
Cheers,
Vince

[NeoPa]

Happy to help Vince :)