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

Multiple Records Based On A Date Range

P: 3
Hello:

I am trying to create multiple records in a table named tblTravel based on the input of a date range in two form controls. The inputs for the form are LastName, TravelDate, EndDate, Event, LocationCity, and LocationState. Everything works perfectly, except for the fact that the dates do not correctly enter into the table. For example, if I enter a date range of March 3, 2008 - March 5, 2008 and click OK, 3 records are generated. The first has the correct date (March 3, 2008), but the next two records return dates of 12-31-1899. The code I am using is pasted below. Please help!!!

Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_onclick()
  2.  
  3. Dim i
  4.         For i = Int(CVDate(Me!fldTravelDate)) To Int(CVDate(Me!fldEndDate))
  5.         If (DatePart("w", i, vbMonday) <> 6) And (DatePart("w", i, vbMonday) <> 7) Then
  6.         DoCmd.RunSQL "INSERT INTO tblTravel(fldName,fldTravelDate,fldEventName,fldLocationCity,fldLocationState) VALUES ('" + Me!fldName + "'," + Format(i, "dd/mm/yy") + ",'" + Me!fldEventName + "','" + Me!fldLocationCity + "','" + Me!fldLocationState + "');"
  7.         End If
  8.         Next i
  9.  
  10. End Sub
Mar 18 '08 #1
Share this Question
Share on Google+
5 Replies


Scott Price
Expert 100+
P: 1,384
Take a good look at lines 4 and the Format(i) function in line 6.

You take a date or string value from the fldTravelDate and convert it to a Variant with subtype Date, then you use the Int() function to return only the integer portion of it. Then in line 6 you are asking the database to look at the integer portion and format it in a specific way.

Try instead of going through the rigmarole of the CVDate and then the Int, using the CDate() function instead. The CDate() function returns a Date/Time value which is stored behind the scenes as an Integer value, and then formatted according to your Regional settings in the Control Panel.

The only real reason to use CVDate() over CDate() is if you are using an older legacy database, or you have a lot of Null values in the fldTravelDate, etc. If you do have Null values, it would be far better, in this case, to test for the null first.

Regards,
Scott
Mar 18 '08 #2

P: 3
Sorry, I have been playing around with this further. The original code was copied and altered from an old post regarding this subject. I am new with working in VBA, and am struggling to fix according to the suggestions mentioned. I wonder if you can help guide me through this.
Mar 18 '08 #3

Scott Price
Expert 100+
P: 1,384
Well, instead of Int(CVDate(Me!fldTravelDate)) you would have CDate(Me!fldTravelDate).

To test for nulls:

Expand|Select|Wrap|Line Numbers
  1. If Not Isnull(Me!fldTravelDate) And Not Isnull(Me!fldEndDate) Then
  2.     For i = CDate(Me!fldTravelDate) To Cdate(Me!fldEndDate)
  3. ..... bla bla blah
  4. Next i
  5. End If
  6.  
Regards,
Scott
Mar 18 '08 #4

P: 3
I have changed the code to appear as below:

Expand|Select|Wrap|Line Numbers
  1.     Private Sub OK_onclick()
  2.  
  3.     Dim i
  4.             For i = CDate(Me!fldTravelDate) To CDate(Me!fldEndDate)
  5.             If Not Isnull(Me!fldTravelDate) And Not Isnull(Me!fldEndDate) Then
  6.             DoCmd.RunSQL "INSERT INTO tblTravel(fldName,fldTravelDate,fldEventName,fldLo  cationCity,fldLocationState) VALUES ('" + Me!fldName + "'," + Format(i, "dd/mm/yy") + ",'" + Me!fldEventName + "','" + Me!fldLocationCity + "','" + Me!fldLocationState + "');"
  7.             End If
  8.             Next i
  9.  
  10.     End Sub
  11.  
I also reversed lines 4 and 5, (I think this is how the last post suggested), but this gave an error related to "if block".
I tried to change line 3 to "Dim i as Date"
I tried playing around with i down in the sql.

Here's the strange thing. When I hold my mouse over i in the sql statement, it returns i = 3/1/08, which is exactly correct. However, in the form, when I click OK and then go check the table column (formatted as short date), it shows 12-31-1899. I changed the table to show general date, and those 3 new records have values of 01:00:00, 02:00:00 and 03:00:00.

Hopefully this information will help shed some light on what's happening and you can help save my last shred of sanity. Thanks again !!!
Mar 19 '08 #5

Scott Price
Expert 100+
P: 1,384
SQL specifies that it only works with date formats of MM/DD/YYYY, which is the North American format. Access is remarkably forgiving when working with date formats, and can correctly interpret many non-ambiguous dates, however the problem arises when you feed it an ambiguous date, like you have given me as an example: 3/1/08... Does that means March 1, 2008, or does it means January 3, 2008? I notice later in your code you have it formatting to the DD/MM/YYYY format, is this your control panel setting?

What happens if you remove the Format(i, "dd/mm/yy") in your SQL statement and replace it simply with i? If this evaluates the dates correctly, and you NEED to have them back in the dd/mm/yy format, you'll have to then wrap your Format() construct in another CDate() or CVDate() function like this: CVDate(Format(i, "dd/mm/yy"))

Also, I notice you are using the + concatenation character. This is fine, but it allows null propagation, which in this instance probably isn't what you really want. If I were you, I'd change the + signs in the SQL statement to & ampersand characters.

Regards,
Scott
Mar 19 '08 #6

Post your reply

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