Multiple Records Based On A Date Range

[jrodcody]

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!!!

[CODE=vb]Private Sub OK_onclick()

Dim i
For i = Int(CVDate(Me!f ldTravelDate)) To Int(CVDate(Me!f ldEndDate))
If (DatePart("w", i, vbMonday) <> 6) And (DatePart("w", i, vbMonday) <> 7) Then
DoCmd.RunSQL "INSERT INTO tblTravel(fldNa me,fldTravelDat e,fldEventName, fldLocationCity ,fldLocationSta te) VALUES ('" + Me!fldName + "'," + Format(i, "dd/mm/yy") + ",'" + Me!fldEventName + "','" + Me!fldLocationC ity + "','" + Me!fldLocationS tate + "');"
End If
Next i

End Sub[/CODE]

[Scott Price]

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

[jrodcody]

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.

[Scott Price]

Well, instead of Int(CVDate(Me!f ldTravelDate)) you would have CDate(Me!fldTra velDate).

To test for nulls:

[CODE=vb]If Not Isnull(Me!fldTr avelDate) And Not Isnull(Me!fldEn dDate) Then
For i = CDate(Me!fldTra velDate) To Cdate(Me!fldEnd Date)
..... bla bla blah
Next i
End If
[/CODE]
Regards,
Scott

[jrodcody]

I have changed the code to appear as below:

[CODE=vb]
Private Sub OK_onclick()

Dim i
For i = CDate(Me!fldTra velDate) To CDate(Me!fldEnd Date)
If Not Isnull(Me!fldTr avelDate) And Not Isnull(Me!fldEn dDate) Then
DoCmd.RunSQL "INSERT INTO tblTravel(fldNa me,fldTravelDat e,fldEventName, fldLo cationCity,fldL ocationState) VALUES ('" + Me!fldName + "'," + Format(i, "dd/mm/yy") + ",'" + Me!fldEventName + "','" + Me!fldLocationC ity + "','" + Me!fldLocationS tate + "');"
End If
Next i

End Sub
[/CODE]
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 !!!

[Scott Price]

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