Access Report Control Date With 2-digit Year 1

[jjlogan]

I haven't been able to format a report control used to display a date field; I want the report just to show mm/dd/yy and not mm/dd/yyyy. If I specify a ControlSource as FormatDateTime([my date field],"mm/dd/yy") I get "Error" in report. The table format for this field is Date/Short. I've searched google and TekTips. Can anyone help me?
Thanks in advance. Jeff

 

[dhookom]

If you change the control source to an expression, you generally have to change the name of the text box to something other than a field name.

Have you tried setting the Format Property?

Duane
Hook'D on Access
MS Access MVP

 

[jjlogan]

Thanks dhookom for the input. I tried both suggestions:
1)Using expression (with FormatDateTime function) I did change the control name by adding a "1" on the end of the name. Still get Error when report displays.
2) Format property for Textbox does not include a 2-digit year.
My expression is =IIF(IsNull([Completion Date]),"--",FormatDateTime([Completion Date],"mm/dd/yy").
Jeff

 

[dhookom]

Sorry I didn't notice but the function name in Access is simply Format() not FormatDateTime().

I just tried leaving the control source at [Completion Date] and set the Format Property to mm/dd/yy;"";"";--

Control Source: [Completion Date]
Format: mm/dd/yy;"";"";--

Duane
Hook'D on Access
MS Access MVP

 

[jjlogan]

dhookom - Not quite there. Here's what I did:
1. Using your suggested format property mm/dd/yy;"";"";-- in the report Textbox I am still getting display in report as 09/20/2003 instead of 09/20/03
2. I went into Control Panel / Regional Settings and changed the Short Date format to Custom m/d/yy, then rebooted. Still getting the yyyy year.
3. In Access, I set the table field format (in Table Design) to mm/dd/yy, but still get yyyy when open the table and also yyyy still displayed in report.

Is something overriding the custom formats we are setting at the control level and field level?
Thanks,
Jeff

 

[jjlogan]

I found my error! In Access / Tools / Options I had previously set the year display to four digits for all databases.
Thanks dhookom for your valuable input; it got me looking in the right direction and I learned more about formating.
Jeff

 

[jjlogan]

dhookom,
I'd like to understand the syntax of your format statement mm/dd/yy;"";"";-- I couldn't find anything in MSDN or google. This expression worked fine ... just wanted to understand it. Thanks again for any help.
Jeff

 

[dhookom]

Dates are basically numbers. You can use up to four sections of the format property to set different formats for:
Positive Values
Negative Values
Zero Values
Null
Each of these format sections is separated by the ";". You can even set different colors based on +, -, 0, or Null.

You should be able to find the full documentation in Help if you search on Format Property Numbers (or similar).

Duane
Hook'D on Access
MS Access MVP