Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: Date flickers between US and English format when creating schedule in VBA

    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.

    Code:
    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:

    Code:
    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

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    frankly, i'm lost right at the beginning:
    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

    what is it?

    Access is **NOT** storing Date (actually DateTime) datatypes as string, so every attempt to squeeze a string into Date necessarily involves casting from one datatype to another.

    when left to it's own devices to cast date-as-string input, Access defaults to (the totally irrational!!) US date format until it gets to an apparent 13th month and then it fudges the cast to (the marginally less irrational!!) English date format.

    Access also understands a watered-down (rational!!) ISO 8601 date-as-string:
    Code:
    "YYYY-MM-DD" and also "YYYY-MM-DD HH:mm:SS"
    also investigate DateSerial() which will produce unambiguous dates.

    izy
    Last edited by izyrider; 01-22-11 at 14:30. Reason: needed [code] tags to kill unwanted smiley
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •