Re: Custom date format not working in ACC2003

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David A" <please@xxxxxxxxxxxxxxxx> wrote in message
news:43944ee7$0$353$da0feed9@xxxxxxxxxxxxxxxxx
> 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
>
> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
> news:ecn6nFa#FHA.1988@xxxxxxxxxxxxxxxxxxxxxxx
>> 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
>> data*** 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
>>
>>
>> "David A" <please@xxxxxxxxxxxxxxxx> wrote in message
>> news:439429ba$0$23293$db0fefd9@xxxxxxxxxxxxxxxxx
>> >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?


.

Loading