VBA code returning micture of American and British dates

[gnoakes]

Hi Everyone,

I'm building a database to organise activity groups run by a local voluntary service I'm involved with. My database has a staff scheduling tool that displays when each staff member is free or not and the date/time of when they have appointments or are due to hold an activity group. This scheduling tool runs off a simple table containing a Primary Key, UnitID (unique staff member code), FromDate, ThurDate and RecordDateTime (time stamp for audit trail purposes).

A second table holds the information relating to each activity group including GroupID (Primary Key), GroupName, Venue, GroupDateTime, GroupSize, GroupRunningTime(i.e. how many weeks group is to run for), RecordDateTime (again time stamp for audit purposes), GroupStaff (lead member of staff running the group).



In order to show on the scheduling tool that a staff member is to hold a group at a certain time each week for however many weeks as determined by the GroupRunningTime field I have written the VBA code below into the "on click" function of a save command button. For the most part this code works fine in appending the right data to the table except for the Dates.

I am finding that any dates which lie between the 1st and the 13th of the month are given in the American format (i.e. mm/dd/yyyy) and any dates between the 13th and the end of the month are given in the British format (i.e. dd/mm/yyyy). I would like all dates to be in the British format. I have tried introducing Format, Format$ and FormatDateTime functions into my code at various points to try and standardised the result but to no avail.

Can anyone help?

Thanks,

George

Private Sub Save_Button4_Click()
On Error GoTo Err_Save_Button4_Click
'Decide whether to append data to tblHourPeriod
'If no Staff member identified then do not append date
If Me.cboUnitID = "" Then
GoTo SaveLine
Else
GoTo AppendLine
End If

'Append data to tblHourPeriod
AppendLine:
Dim Field1
Dim Field2 As Date
Dim Field3 As Date
Dim strSQL As String
For Counter = 1 To Me.GroupRunningTime
Field1 = Me.cboUnitID
Field2 = DateAdd("d", (Counter - 1) * 7, Me.GroupDateTime)
'Activity Groups run for 1 hour
Field3 = DateAdd("h", 1, Field2)
strSQL = "INSERT INTO tblHourPeriod (UnitID, FromDate, ThruDate, ColorKey) VALUES (" & Field1 & ", #" & Field2 & "#, #" & Field3 & "#,'P');"
DoCmd.RunSQL strSQL
Next
'Save Record
SaveLine:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Save_Button4_Click:
Exit Sub
Err_Save_Button4_Click:
MsgBox Err.Description
Resume Exit_Save_Button4_Click

End Sub

[orange]

A couple of observations that you may wish to consider. These may not be the cause of any issue, but are not considered "good practices".

When you Dim a variable( Dim Field1) and don't provide a data type, the variable will be Variant by default.

Counter has a meaning within Access. That is the method previously used to define an autonumber field.
See http://allenbrowne.com/AppIssueBadWord.html

Is UnitID a number or string?

There is no formatting of Field2 or Field3, so I expect these are being saved in the default mm/dd/yy format as fromDate Thrudate in table tblHourPeriod.