Date format problems in Access2000, need your help!

-I have a tblPayDates, and the field DueDate as Date/Time Short format ex..31/11/03 which is dd/mm/yy Access 2000 it automatically assigns it that way
-I have a form ‘frmPayDate’, with 3 fields clientID, firstdate, period, and an ‘insert’ button to automatically insert the due dates into the tblPayDate without having to type the due dates over and over again. Also have a subform sfrmPayDate with 2 fields, clientID, DateDue which is connected to the tblPayDate to display the Due Dates after they are inserted byt the insert button. The ‘insert’ button has the following code:

Private Sub cmdAddDates_Click()

Dim x As Integer
Dim d As Date
Dim dd As Date
Dim strSQL As String

On Error GoTo Err_cmdDates

x = 0

If [txtquotas] >= [period] Then
Beep
MsgBox "Can't add more dates", vbCritical + vbOKOnly, "Atention"
Exit Sub
Else
DoCmd.SetWarnings False

Do While x < [period]
d = DateAdd("m", x, [firstdate])
dd = d

strSQL = "insert into tblPayDates values ('" & Me.clientID.Value & " ', #" & dd & "#);"

DoCmd.RunSQL strSQL 'append pay dates

x = x + 1

Loop
End If

DoCmd.SetWarnings True

Exit_cmdDates:
Exit Sub

Err_cmdDates:
Resume Exit_cmdDates

End Sub

When I open the ‘frmPayDate’ again, and in the subform sfrmPayDate it displays the Due Date as mm/dd/yy format and not like I set it up in the tblPayDates as DateDue, short format, ‘dd/mm/yy’. How can I make it to display in the ‘dd/mm/yy’ format and not the ‘mm/dd/yy’ format??? OR How can I make the Access Date/Time format as ‘mm/dd/yy’?? That way, I can have all my dates in one format!!

Also, if I have a date that is past the 12th day, example, ‘dd/mm/yy’ format 13/11/03 and I open the sub form where it displays the Due Date, the Due Date field is displayed as ‘03/11/13’ and so on…… I have the file online for download to see what I mean! For some reason I’ve spent too much time trying to figure it out, please help me!!! Thank you.

Don't know if this will help, but


When I look at your tblPayDates the dates look like, mm/dd/yy. This is because my desktop system settings for Shortdate is mm/dd/yy.

Now if you system short date is dd/mm/yy then you need to format the text box on your sub form to show it as shortdate (which it looks like it does).

Are you sure that some items are formated one way and other are formated another way. The reason I ask is because if you have everything formated to shortdate (being desktop driven format) it should be all the same.

FYI There is a difference between input mask and format

Good Luck

S-