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

Inserting multiple rows based on a Date range

P: 5
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'.
Expand|Select|Wrap|Line Numbers
  1. Dim RepeatEvent
  2.  
  3. For RepeatEvent = Int(CDate(Me!txtRDateStart)) To Int(CDate(Me!txtRDateEnd))
  4.  
  5. DoCmd.RunSQL "INSERT INTO tblReminder (RDescription, RDate, RTime) " & _
  6.              "SELECT Forms![frmReminder_New]!txtRDescription AS Expr1," & _
  7.              Format$(RepeatEvent, "dd/mm/yyyy") & _
  8.              ", [Forms]![frmReminder_New]![txtRTime] AS Expr3;"
  9.  
  10. Next RepeatEvent
Any help would be greatly appreciated.
Mar 13 '10 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1.              "SELECT Forms![frmReminder_New]!txtRDescription AS Expr1,'" & _
  2.              Format$(RepeatEvent, "dd/mm/yyyy") & _
  3.              "', [Forms]![frmReminder_New]![txtRTime] AS Expr3;"
If it's a date then they should be :
Expand|Select|Wrap|Line Numbers
  1.              "SELECT Forms![frmReminder_New]!txtRDescription AS Expr1,#" & _
  2.              Format$(RepeatEvent, "m/d/yyyy") & _
  3.              "#, [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.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1.              "SELECT Forms![frmReminder_New]!txtRDescription AS Expr1,'" & _
  2.              Format$(RepeatEvent, "dd/mm/yyyy") & _
  3.              "', [Forms]![frmReminder_New]![txtRTime] AS Expr3;"
If it's a date then they should be :
Expand|Select|Wrap|Line Numbers
  1.              "SELECT Forms![frmReminder_New]!txtRDescription AS Expr1,#" & _
  2.              Format$(RepeatEvent, "m/d/yyyy") & _
  3.              "#, [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.
Mar 13 '10 #2

P: 5
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
Mar 14 '10 #3

NeoPa
Expert Mod 15k+
P: 31,489
Happy to help Vince :)
Mar 15 '10 #4

Post your reply

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