Problems with Date when creating a schedule using VBA (1 Viewer)

slaterino

Registered User.
Local time
Today, 11:32
Joined
Dec 2, 2008
Messages
14
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
 

vbaInet

AWF VIP
Local time
Today, 11:32
Joined
Jan 22, 2010
Messages
26,374
The first thing you should do is to set the Format property of the text box's property sheet to General Date or Short Date. This uses the regional settings of your computer, so it's set to U.K. it will adhere to that format.

I will try the above first, but if that fails then you can force it:

http://www.mvps.org/access/datetime/date0005.htm

Use the second one. Just swap the dd and mm around.
 

slaterino

Registered User.
Local time
Today, 11:32
Joined
Dec 2, 2008
Messages
14
I have tried using the Format function but the problem is that it converts it into a String as opposed to Date, and so ends up giving some crazy date in 1989.

Is there any way to use Format as a Date?

I wish there was!
 

slaterino

Registered User.
Local time
Today, 11:32
Joined
Dec 2, 2008
Messages
14
Sorry, all my form fields, as well as the field in the table which the date is being saved to, are set as Short Date. This unfortunately makes little difference. I have also tried using Medium Date as I thought the combination of dd-mmm-yy might be harder to get wrong but it doesn't work with that either!

Hmmm....
 

vbaInet

AWF VIP
Local time
Today, 11:32
Joined
Jan 22, 2010
Messages
26,374
And this?

Format([Datefield], "dd\/mm\/yyyy")

What is the regional settings of the computer?
 

slaterino

Registered User.
Local time
Today, 11:32
Joined
Dec 2, 2008
Messages
14
The problem with Format([Datefield], "dd\/mm\/yyyy") is that it then converts the Date into a String and I end up with a date some time in 1989.

The computers that it will be used on are all based in the UK.
 

vbaInet

AWF VIP
Local time
Today, 11:32
Joined
Jan 22, 2010
Messages
26,374
DateValue(format("12/31/2010", "dd\/mm/yyyy"))

Also, note that dates used when querying your database must be in the American format.
 

slaterino

Registered User.
Local time
Today, 11:32
Joined
Dec 2, 2008
Messages
14
I like your thinking, and I have just tried it using it on the following line:

#" & DateValue(Format(datThis, "dd/mm/yyyy")) & "#

But alas, no luck. The results are as per usual. It does feel like we may be getting closer though!
 

vbaInet

AWF VIP
Local time
Today, 11:32
Joined
Jan 22, 2010
Messages
26,374
In what context are you using it? In a query? Comparison in vba?
 

Users who are viewing this thread

Top Bottom