01-21-11, 12:47 #1Registered User
- Join Date
- Nov 2008
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.
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
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
Thanks for any help!
01-22-11, 14:25 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
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:
"YYYY-MM-DD" and also "YYYY-MM-DD HH:mm:SS"
Last edited by izyrider; 01-22-11 at 14:30. Reason: needed [code] tags to kill unwanted smileycurrently using SS 2008R2