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

Date Range problem

P: 2
I have several ranges of dates (2007-03-15 - 2007-03-21, format YYYY,MM,DD) and I need to list all of the dates in the range and not sure how to do this. I wrote an OnLoad procedure that should run each time the form is opened. I want the procedure to insert the dates between the range into the the table 'travel_dates" for each trip with the authorization number being the linking number. I keep getting Runtime error 3061. Am I way off base? Any thoughts would be helpful!

Thanks,

Jen
--------

Private Sub Form_Load()

Dim Datecount As Integer 'count down number
Dim Departday As Date 'last day of private
Dim IntervalDay As String 'interval in days to add to the date
Dim ALdate As Date 'new date calculated
Dim TANo As String 'travel authorization number
Dim dbs As Database

Set dbs = CurrentDb

IntervalDay = "d" 'set the interval
Datecount = Me.NumberOfNights 'set number of nights credited to private
ALdate = Me.ArrivalDate 'set first private date

TANo = Me.[2004_private.TA Number]

Do While Datecount <> 0
dbs.Execute "INSERT INTO travel_dates (date_day, date_ta_no) VALUES (ALdate, TANo);" 'insert new dates
ALdate = DateAdd(IntervalDay, 1, ALdate) 'calculate the date on private
Datecount = Datecount - 1
Loop

dbs.Close

End Sub
May 21 '07 #1
Share this Question
Share on Google+
3 Replies

Rabbit
Expert Mod 10K+
P: 12,441
"INSERT INTO travel_dates (date_day, date_ta_no) VALUES (ALdate, TANo);"

Anything within a string is read as is. The SQL engine will have no idea what ALdate or TANo is. You have to pass it by value.

Expand|Select|Wrap|Line Numbers
  1. "INSERT INTO travel_dates (date_day, date_ta_no) VALUES (#" & ALdate & _
  2. "#, " & TANo & ");" 
May 21 '07 #2

P: 2
"INSERT INTO travel_dates (date_day, date_ta_no) VALUES (ALdate, TANo);"

Anything within a string is read as is. The SQL engine will have no idea what ALdate or TANo is. You have to pass it by value.

Expand|Select|Wrap|Line Numbers
  1. "INSERT INTO travel_dates (date_day, date_ta_no) VALUES (#" & ALdate & _
  2. "#, " & TANo & ");" 
Thank you! After I looked at your code for a minute slowly I remembered! Thank you!

Jen
May 22 '07 #3

Rabbit
Expert Mod 10K+
P: 12,441
Thank you! After I looked at your code for a minute slowly I remembered! Thank you!

Jen
Not a problem, good luck.
May 22 '07 #4

Post your reply

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