Microsoft Access 2010 Date Format

Question

Hello

I'm having trouble with the format of Dates for an attendance form, VBA script and tables in Access 2010. I have a form to record attendance. When a user clicks the check box beside a persons name, it inserts the current date into a table called tblMadAttend. The problem I'm having is with the format of the date when it gets inserted into the table. For the first 12 days of January, the date format was 01/02/2015, 01/05/2015, 01/08/2015, 01/09/2015, 01/12/2015. For January 13th, the date format in the table was 13/01/2015.

I've tried formatting the date using the command Format(Date, "mm/dd/yyyy") but it still inserts the date as 13/01/2015 and 14/01/2015. I've tried to set the Table field to format Short Date with an input mask of 00/00/0000 and removed the format. I've also played with my system date settings which switched the first 12 dates to 02/01/2015, 05/01/2015, 08/01/2015, 09/01/2015 and 12/01/2015. The date for the 13th changed to 01/13/2015.

The form also checks to see if the person has been marked present or not, and if they have been marked  present  for the current date, it checks the check box. The problem is the different date formats don't always match.

The code I have is as follows.

Option Compare Database

Dim sqlStr As String

Dim Msg, Style, Response

Private Sub cmdAttend_Click()

    If (IsNull(DLookup("[ID]", "tblMadAttend", "[PID]='" & Me.txtID.Value & "' and [AttDate]=#" & Date & "#"))) Then

        Me.chkAttend.Value = True

        sqlStr = "INSERT into tblMadAttend(PID, AttDate) VALUES('" & Me.txtID.Value & "', #" & Date & "#)"

        CurrentDb.Execute sqlStr, dbFailOnError

    Else

        Msg = "This person has been recorded present already for today. Do you want to make them absent?"

        Style = vbYesNo + vbCritical + vbDefaultButton2

        Response = MsgBox(Msg, Style)

        If Response = vbYes Then

            sqlStr = "DELETE from tblMadAttend WHERE [PID]='" & Me.txtID.Value & "' and [AttDate]=#" & Date & "#"

            CurrentDb.Execute sqlStr, dbFailOnError

            MsgBox "Record Deleted"

            Me.chkAttend.Value = False

        End If

    End If

End Sub

Private Sub Form_Load()

    Dim rcount As Integer

    Dim i As Integer

    rcount = DCount("ID", "qryMadAttendance1")

    For i = 1 To rcount

        If (IsNull(DLookup("[ID]", "tblMadAttend", "[PID]='" & Me.txtID.Value & "' and [AttDate]=#" & Date & "#"))) Then

            Me.chkAttend.Value = False

        Else

            Me.chkAttend.Value = True

        End If

        DoCmd.GoToRecord , , acNext

    Next i

    DoCmd.GoToRecord , , acFirst

End Sub

I've tried everything I can think of to get a date format of dd/mm/yyyy but for some reason the first 12 days of the month are seeing the 01 as the day instead of the month.

Any help would be very much appreciated.

Thanks,

Ryan Doyle

Answer 1

Without seeing the database it is impossible to say.

I quickly tried to replicate your table and have no issue:

952	barsam	1/14/2015
953	cheyil	1/14/2015
954	ellian	1/14/2015
955	greson	1/14/2015
971	barsam	1/7/2015
972	cheyil	1/7/2015
973	ellian	1/7/2015
974	greson	1/7/2015
975	barsam	1/8/2015
976	cheyil	1/8/2015
977	ellian	1/8/2015
978	greson	1/8/2015

so it has to be a setting in your table, forms, ..., but without access to your db I simply cannot say where.

What happens if you try to create a new table without playing around with any formats?

Answer 2

hi,

here is my sample table.

952	barsam	14/01/2015
953	cheyil	14/01/2015
954	ellian	14/01/2015
955	greson	14/01/2015
971	barsam	01/07/2015
972	cheyil	01/07/2015
973	ellian	01/07/2015
974	greson	01/07/2015
975	barsam	01/08/2015
976	cheyil	01/08/2015
977	ellian	01/08/2015
978	greson	01/08/2015

my regional settings are set to dd/mm/yyyy. it's reading the 08 and 07 as months even though on my computer it is the day. i changed my calendar back to the 7th and changed my regional settings to mm/dd/yyyy it still reads 08 aand 07 as the month in the table but everywhere else 07 and 08 are the day. i even created a variable named tdate and stored the date to it and tried using it. tdate will have right format but as soon as it goes to the table same problem.

i'm either missing something or just plan stupid.

Thanks.

Answer 3

How is the current date inserted?

The format displayed in the table is truly not important.  One should not be working in tables.  The real question is when output in a form or query is it correct when you format it the way you wish it displayed.  If you open a date picker is it displayed properly.

The storage of the data is not important, it is the display in your forms and reports that is important.

Scott is right that this is controlled primarily by your Regional Settings.

There are several thing to note.

• Date entry should ALWAYS be done through a Date Picker
• You can specify the default format to use in the table field properties
• You can specify a control's format property in a form and report.
• In VBA one should use a function such as SQLDate so VBA interprets the dates properly.

Lastly can you provide a sample version of your db for us to look over.

Answer 4

hi, i changed my regional settings and it still happens. Actually does complete opposite if I change my regional settings to mm/dd/yyyy from dd/mm/yyyy. for some reason the first 12 days of the month are switching the day and month.

Answer 5

The date format is based on your Regional Settings.