When creating a schedule in Visual Basic date changes between US and English format

[slaterino]

I have designed a way of creating a schedule using a Microsoft Access form and Visual Basic, which works exactly how I want, except for the fact that the date doesn't know which continent it's in. I know a lot of people have had problems with date, and I have looked at lots of solutions on forums, but none of these quite help with my problem.



Essentially in my form you create a schedule, which can either be one date, or formed from multiple dates. When multiple dates are chosen you select a Start Date and End Date and then a selection of days (Mon, Tues, Wed) and weeks (All weeks, 1st week, 2nd week) where there will be scheduled dates. This works great except that for dates greater than the 12th day of the month it shows as US, while for the others it shows in English format, i.e.

2nd March = 03/02/2011 (would display as US)
20th March = 20/03/2011 (displayed in English format)

My preference would be for the dates to all show in the English format, but wouldn't mind if I could get them all to display in the US format. The problem is that it's always a mix of both. Here's my VB coding for the dates. Is there anything I could add which would ensure that the format is correct. I've tried using the Format function but it's produced exactly the same results.

Private Sub cmdBuildSchedule_Click()
    Dim datThis As Date
    Dim strSQL As String
    Dim db As DAO.Database
    Dim intDOW As Integer 'day of week
    Dim intDIM As Integer 'Day in month
    If Me.grpRepeats = 2 Then
        If Not CheckDates() Then
            Exit Sub
        End If
    End If
    If Not CheckTimes() Then
        Exit Sub
    End If
    
    Set db = CurrentDb
    If Me.grpRepeats = 2 Then 'need to loop through dates
        For datThis = Format(Me.txtStartDate, "dd/mm/yyyy") To Format(Me.txtEndDate, "dd/mm/yyyy")
            intDIM = GetDIM(datThis)
            intDOW = Weekday(datThis)
            If Me("chkDay" & intDIM & intDOW) = True Or _
                    Me("chkDay0" & intDOW) = True Then
                strSQL = "INSERT INTO tbl_temp_schedule_dates ( tscDate ) " & _
                    "Values(#" & _
                    datThis & ")"
                db.Execute strSQL, dbFailOnError
            End If
        Next
     Else  'dates are there, just add the title, notes, times, location, Activity
        strSQL = "Update tbl_temp_schedule_dates Set "
            
        db.Execute strSQL, dbFailOnError
    End If
    Me.sfrm_temp_schedule_edit.Requery
    MsgBox "Temporary schedule built. " & _
        "You can now edit the schedule and " & _
        "append to the permanent schedule.", vbOKOnly + vbInformation, "Temp schedule complete"
End Sub

I've stripped out the mentions of the other fields as these didn't really need to be in there.

Here's the function I use for when it's a multiple date activity:

Private Sub grpRepeats_AfterUpdate()
    Dim ctl As Control
    Dim intCounter As Integer
    Dim intWeek As Integer
    Dim intDay As Integer
    Me.txtEndDate.Visible = (Me.grpRepeats = 2)
    Me.txtStartDate.Visible = (Me.grpRepeats = 2)
    Me.sfrm_temp_schedule.Visible = (Me.grpRepeats = 1)
    For intWeek = 0 To 5
        For intDay = 1 To 7
            Set ctl = Me("chkDay" & intWeek & intDay)
            ctl.Visible = (Me.grpRepeats = 2)
            ctl.Value = 0
        Next
    Next
    Select Case Me.grpRepeats
        Case 2  'repeating
        Case 1
    End Select
End Sub

Sorry for such a long entry. Does anyone have any idea of how to cure this problem??

Thanks for any help!
Russ

[RuralGuy]

Does this link help: http://allenbrowne.com/ser-36.html