473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date Range problem

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
3 1574
Rabbit
12,516 Expert Mod 8TB
"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
"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
12,516 Expert Mod 8TB
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

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

Similar topics

2
by: androtech | last post by:
Hello, I'm looking for a function that returns a date range for a specified week number of the year. I'm not able to find functions like this anywhere. Any pointers/help would be much...
8
by: peashoe | last post by:
I have an asp page that uses a calendar.js (pop-up) file to add an exact date format in the text field (txtDDate). My problem is I need some javascript that sets an alert that does not allow them...
5
by: BlackFireNova | last post by:
I need to write a report in which one part shows a count of how many total records fall within the working days (Monday - Friday) inside of a (prompted) given date range, in a particular...
6
by: alexanderpope11 | last post by:
Hello, how do I write this SQL: I would like to check the Main table for invalid rows. An invalid row is: any row where the Start_date to stop_date range overlaps an invalid date in the Code...
18
by: dfetrow410 | last post by:
Anyone have some code that will do this? Dave
67
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 ...
3
by: Deano | last post by:
The short version; In short, given one date range (start and end dates) how can I find the period that overlaps with another date range? The long version; I have knocked up a little application...
2
by: grego9 | last post by:
I have a problem in Excel 2000. I have written some VBA code that transfers data from the current excel workbook to a file called "Deal Input2.xls". Everything transfers ok apart from the date in...
9
by: Mo | last post by:
After a little PHP education, my first project (to get my feet wet) is making an employee time-tracking program for our small business. *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY...
19
by: phill86 | last post by:
Hi I am re-posting this thread because it has become very confusing and I have got some way to solving the problem so it is a slightly different question from the initial thread. here is the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.