Custom date format not working in ACC2003

[David A]

I am working in a country where dates are printed d/m/y. I find that users
often don't have their Regional date settings set correctly so in Access
projects we have always hardcoded the date format in reports as "dd/mm/yyyy"
or "dd/mm/yy". I've done this in projects for years using mostly Access 97
without a problem. Now we've "upgraded" [sic] to Access 2003 and it doesn't
work. No matter what I do, the date fields in reports always format with the
default regional date.

e.g. for a Text box in a report
Control Source: TranDate
Format: dd/mm/yy

The format drop-down box is showing only the default date and time formats,
so I guess the system is recognising that the underlying data type is a
date. But it won't act if we enter a custom date format. Likewise "ddd mmm"
and other such variants don't work either.

My work-around solution has been to change the Control Source to
=Format([Trandate],"dd/mm/yy") but that's not satisfactory and will give an
error if the date is a null value. Plus it's a real pain to go through all
the reports in all our projects and fix up what should be proper behaviour.

Any suggestions to fix this?

 

[Allen Browne]

Hi David.

I've worked with Access for 13 years in a dmy country, but do not experience
the problem you describe.

IME, if a text box is bound directly to a date/time field in a table, you
can set the Format property of the control on the form/report or of the
field in the table, and it will display as instructed. (The only exception I
know if is the nonsense option for Display 4-digit Years which overrides
everything.)

OTOH, Jet 4 (Access 2000 and later) is *much* worse than JET 3.5 at
recognising the data type of a calculated field. Even if the field is
generated using something like DateSerial() that obviously outputs dates,
you still have to explicitly typecast with CVDate() before you can trust JET
4 to recognise it correctly. The clue that it has it wrong is that the
datasheet view of the query outut left-aligns the field like text, instead
of right-aligning it like a date. And certainly, once JET fails to
understand the type, setting the Format of the control won't rescue it.

The techniques I use to ensure Access interprets my dates correctly are
described here:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Despite the fact that the Access documentation states that CVDate() exists
just for backward compatibility (with Access 2, which did not have a Date
type in VBA), it's a life-saver: the function accepts and returns Nulls, yet
JET understands the type correctly.

BTW, on a philosophical level, I always respect the user's date settings. A
savvy user is rightly annoyed if I force my view of the universe on him, and
an ignorant user won't learn if I try to fix his issues for him.

If you are interested in locating all text boxes that are formatted as
dd/mm/yy and clearing the format programmatically, the function below
illustrates how to do it. (You would also need to clear the Format of the
Field in the TableDef.)

Public Function FixDates()
Dim accObj As AccessObject
Dim strDoc As String

For Each accObj In CurrentProject.AllForms
strDoc = accObj.Name
DoCmd.OpenForm strDoc, acDesign, windowmode:=acHidden

If FixDateSub(Forms(strDoc)) > 0 Then
Forms(strDoc).Visible = True
Else
DoCmd.Close acForm, strDoc
End If
Next

For Each accObj In CurrentProject.AllReports
strDoc = accObj.Name
DoCmd.OpenReport strDoc, acDesign, windowmode:=acHidden

If FixDateSub(Reports(strDoc)) > 0 Then
Reports(strDoc).Visible = True
Else
DoCmd.Close acReport, strDoc, acSaveYes
End If
Next
End Function

Private Function FixDateSub(obj As Object) As Long
Dim ctl As Control
Dim lngKt As Long

For Each ctl In obj.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Format = "dd/mm/yy" Then
Debug.Print obj.Name & "." & ctl.Name
ctl.Format = vbNullString
lngKt = lngKt + 1
End If
End If
Next
FixDateSub = lngKt
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am working in a country where dates are printed d/m/y. I find that users
often don't have their Regional date settings set correctly so in Access
projects we have always hardcoded the date format in reports as
"dd/mm/yyyy"
or "dd/mm/yy". I've done this in projects for years using mostly Access 97
without a problem. Now we've "upgraded" [sic] to Access 2003 and it
doesn't
work. No matter what I do, the date fields in reports always format with
the
default regional date.

e.g. for a Text box in a report
Control Source: TranDate
Format: dd/mm/yy

The format drop-down box is showing only the default date and time
formats,
so I guess the system is recognising that the underlying data type is a
date. But it won't act if we enter a custom date format. Likewise "ddd
mmm"
and other such variants don't work either.

My work-around solution has been to change the Control Source to
=Format([Trandate],"dd/mm/yy") but that's not satisfactory and will give
an
error if the date is a null value. Plus it's a real pain to go through all
the reports in all our projects and fix up what should be proper
behaviour.

Any suggestions to fix this?

 

[David A]

Allen,

That's it! "Display 4-digit Years". Seeing that option in General settings I
assumed it was a useful one to check. Obviously not. Unchecking that option
fixed the problem.

So what does that option do?

David

Hi David.

I've worked with Access for 13 years in a dmy country, but do not experience
the problem you describe.

IME, if a text box is bound directly to a date/time field in a table, you
can set the Format property of the control on the form/report or of the
field in the table, and it will display as instructed. (The only exception I
know if is the nonsense option for Display 4-digit Years which overrides
everything.)

OTOH, Jet 4 (Access 2000 and later) is *much* worse than JET 3.5 at
recognising the data type of a calculated field. Even if the field is
generated using something like DateSerial() that obviously outputs dates,
you still have to explicitly typecast with CVDate() before you can trust JET
4 to recognise it correctly. The clue that it has it wrong is that the
datasheet view of the query outut left-aligns the field like text, instead
of right-aligning it like a date. And certainly, once JET fails to
understand the type, setting the Format of the control won't rescue it.

The techniques I use to ensure Access interprets my dates correctly are
described here:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Despite the fact that the Access documentation states that CVDate() exists
just for backward compatibility (with Access 2, which did not have a Date
type in VBA), it's a life-saver: the function accepts and returns Nulls, yet
JET understands the type correctly.

BTW, on a philosophical level, I always respect the user's date settings. A
savvy user is rightly annoyed if I force my view of the universe on him, and
an ignorant user won't learn if I try to fix his issues for him.

If you are interested in locating all text boxes that are formatted as
dd/mm/yy and clearing the format programmatically, the function below
illustrates how to do it. (You would also need to clear the Format of the
Field in the TableDef.)

Public Function FixDates()
Dim accObj As AccessObject
Dim strDoc As String

For Each accObj In CurrentProject.AllForms
strDoc = accObj.Name
DoCmd.OpenForm strDoc, acDesign, windowmode:=acHidden

If FixDateSub(Forms(strDoc)) > 0 Then
Forms(strDoc).Visible = True
Else
DoCmd.Close acForm, strDoc
End If
Next

For Each accObj In CurrentProject.AllReports
strDoc = accObj.Name
DoCmd.OpenReport strDoc, acDesign, windowmode:=acHidden

If FixDateSub(Reports(strDoc)) > 0 Then
Reports(strDoc).Visible = True
Else
DoCmd.Close acReport, strDoc, acSaveYes
End If
Next
End Function

Private Function FixDateSub(obj As Object) As Long
Dim ctl As Control
Dim lngKt As Long

For Each ctl In obj.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Format = "dd/mm/yy" Then
Debug.Print obj.Name & "." & ctl.Name
ctl.Format = vbNullString
lngKt = lngKt + 1
End If
End If
Next
FixDateSub = lngKt
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am working in a country where dates are printed d/m/y. I find that users
often don't have their Regional date settings set correctly so in Access
projects we have always hardcoded the date format in reports as
"dd/mm/yyyy"
or "dd/mm/yy". I've done this in projects for years using mostly Access 97
without a problem. Now we've "upgraded" [sic] to Access 2003 and it
doesn't
work. No matter what I do, the date fields in reports always format with
the
default regional date.

e.g. for a Text box in a report
Control Source: TranDate
Format: dd/mm/yy

The format drop-down box is showing only the default date and time
formats,
so I guess the system is recognising that the underlying data type is a
date. But it won't act if we enter a custom date format. Likewise "ddd
mmm"
and other such variants don't work either.

My work-around solution has been to change the Control Source to
=Format([Trandate],"dd/mm/yy") but that's not satisfactory and will give
an
error if the date is a null value. Plus it's a real pain to go through all
the reports in all our projects and fix up what should be proper
behaviour.

Any suggestions to fix this?

 

[Allen Browne]

Microsoft introduced that option in the goldrush days of Y2k.

It forces the year to display as 4 digits in dates, and so messes up
(overrides) specific date formats. The results are really dumb. For example,
if you do use a date/time field for credit card expiry, and you want that to
print as mmyy, you get 6 digits, and if you built your text box wide enough
for 4 digits, the last 2 digits are missing in the display. Utter junk.

Fortunately you can turn it off.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

That's it! "Display 4-digit Years". Seeing that option in General settings
I
assumed it was a useful one to check. Obviously not. Unchecking that
option
fixed the problem.

So what does that option do?

David

Hi David.

I've worked with Access for 13 years in a dmy country, but do not experience
the problem you describe.

IME, if a text box is bound directly to a date/time field in a table, you
can set the Format property of the control on the form/report or of the
field in the table, and it will display as instructed. (The only
exception I
know if is the nonsense option for Display 4-digit Years which overrides
everything.)

OTOH, Jet 4 (Access 2000 and later) is *much* worse than JET 3.5 at
recognising the data type of a calculated field. Even if the field is
generated using something like DateSerial() that obviously outputs dates,
you still have to explicitly typecast with CVDate() before you can trust JET
4 to recognise it correctly. The clue that it has it wrong is that the
datasheet view of the query outut left-aligns the field like text,
instead
of right-aligning it like a date. And certainly, once JET fails to
understand the type, setting the Format of the control won't rescue it.

The techniques I use to ensure Access interprets my dates correctly are
described here:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Despite the fact that the Access documentation states that CVDate()
exists
just for backward compatibility (with Access 2, which did not have a Date
type in VBA), it's a life-saver: the function accepts and returns Nulls, yet
JET understands the type correctly.

BTW, on a philosophical level, I always respect the user's date settings. A
savvy user is rightly annoyed if I force my view of the universe on him, and
an ignorant user won't learn if I try to fix his issues for him.

If you are interested in locating all text boxes that are formatted as
dd/mm/yy and clearing the format programmatically, the function below
illustrates how to do it. (You would also need to clear the Format of the
Field in the TableDef.)

Public Function FixDates()
Dim accObj As AccessObject
Dim strDoc As String

For Each accObj In CurrentProject.AllForms
strDoc = accObj.Name
DoCmd.OpenForm strDoc, acDesign, windowmode:=acHidden

If FixDateSub(Forms(strDoc)) > 0 Then
Forms(strDoc).Visible = True
Else
DoCmd.Close acForm, strDoc
End If
Next

For Each accObj In CurrentProject.AllReports
strDoc = accObj.Name
DoCmd.OpenReport strDoc, acDesign, windowmode:=acHidden

If FixDateSub(Reports(strDoc)) > 0 Then
Reports(strDoc).Visible = True
Else
DoCmd.Close acReport, strDoc, acSaveYes
End If
Next
End Function

Private Function FixDateSub(obj As Object) As Long
Dim ctl As Control
Dim lngKt As Long

For Each ctl In obj.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Format = "dd/mm/yy" Then
Debug.Print obj.Name & "." & ctl.Name
ctl.Format = vbNullString
lngKt = lngKt + 1
End If
End If
Next
FixDateSub = lngKt
End Function

I am working in a country where dates are printed d/m/y. I find that users
often don't have their Regional date settings set correctly so in
Access
projects we have always hardcoded the date format in reports as
"dd/mm/yyyy"
or "dd/mm/yy". I've done this in projects for years using mostly Access 97
without a problem. Now we've "upgraded" [sic] to Access 2003 and it
doesn't
work. No matter what I do, the date fields in reports always format
with
the
default regional date.

e.g. for a Text box in a report
Control Source: TranDate
Format: dd/mm/yy

The format drop-down box is showing only the default date and time
formats,
so I guess the system is recognising that the underlying data type is a
date. But it won't act if we enter a custom date format. Likewise "ddd
mmm"
and other such variants don't work either.

My work-around solution has been to change the Control Source to
=Format([Trandate],"dd/mm/yy") but that's not satisfactory and will
give
an
error if the date is a null value. Plus it's a real pain to go through all
the reports in all our projects and fix up what should be proper
behaviour.

Any suggestions to fix this?