Re: Custom date format not working in ACC2003

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
>> 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
>>
>>
>> "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?


.

Relevant Pages

  • Re: Price Data Formatting
    ... When you load in a datafile of a stock or commodity, ... only 3 digits following the decimal rather than 4. ... So when I display prices in my application, I want to use the SAME format as ... It doesn't fit well in my textboxes for display. ...
    (microsoft.public.vb.general.discussion)
  • Re: Can General format numbers be mistaken as text?
    ... how many digits that may be. ... It's fine if you want to use General format. ... Perhaps your coworker is worried that there may be situations where Excel will change the cell format from General to Text automagically, just as it changes from General to Number, Currency or Percentage. ... mean it will "display whatever the user types" exactly as it is entered. ...
    (microsoft.public.excel.misc)
  • Re: Standard number format and number of digits
    ... I was wondering if there is a way to set the number of digits that are ... I prefer using the standard format, I found it more readable than the ... apart that it clutters the display with long numbers. ...
    (comp.sys.hp48)
  • Re: Standard number format and number of digits
    ... I was wondering if there is a way to set the number of digits that are displayed and/or used by the calculator in the standard number format? ... I prefer using the standard format, I found it more readable than the others, apart that it clutters the display with long numbers. ...
    (comp.sys.hp48)
  • RE: display data in report from tables with same ID
    ... That's what occurs by default in reports. ... Typically you use group header sections and/or subreports to get the display ... format you want. ... lines of the details are displayed multiple times because there is multiple ...
    (microsoft.public.access.reports)