I need a date format with leading zeros.

[PURVIANCE]

I can't seem to get a date format with leading zeros such as 06/01/06.
Instead, it gives me 6/01/06. How do I design my table to provide me with a
leading zero format?

 

[Tom Lake]

I can't seem to get a date format with leading zeros such as 06/01/06.
Instead, it gives me 6/01/06. How do I design my table to provide me with
a
leading zero format?

You have to set the date format in the Control Panel / Regional and Language
Options applet before Access will recognize it.

Tom Lake

 

[BruceM]

A date is stored behind the scenes as a serial number. You can format it as
you choose. You haven't mentioned any details, so I will assume the date is
being displayed in a text box on a form or report. In that case, you can
open the text box property sheet (View > Properties), click the Format tab,
and put this into the Format line:
mm/dd/yy
You can do the same thing in the table format, but you can override that
with the text box properties. I prefer to format at the level on which the
information is to be displayed rather than in the record source (table).
You can also change the regional settings as described if you prefer the
mm/dd/yy format in all cases, but you can format the data as you choose
regardless of those settings.

 

[BruceM]

My point was intended to be that a value stored in a date/time field can be
formatted in any legitimate date/time format, regardless of the appearance
of the number in the table. I agree that mentioning the serial number
probably added little if anything to the op's understanding. However, I
wonder why you chose to comment on that, but not on the statement "You have
to set the date format in the Control Panel / Regional and Language Options
applet before Access will recognize it." That date format will affect, for
instance, the default short date format, but it is not the last word.
I don't know what you mean "double float under the covers".
I wonder why you assumed I would recommend using Date() + 7 for next weeks
date (rather than DateAdd). Having said that, Date() + 7 seems to work
(probably because 7 is added to the date serial number), although there may
be limitations of which I am unaware.
I appreciate your taking the time to read and comment.

 

[BruceM]

Replies inline

Now that I think about it, I don't know what you mean by 'serial
number'. What I mean is a DATETIME value is persisted in storage
('under the covers') as a numeric in double precision floating point
representation (http://en.wikipedia.org/wiki/Floating_point). I guess
what you mean by 'serial' is that the integer portion represents the
number of whole days that have elapsed since 1899-12-30 and the decimal
part ultimately represents the number of seconds that have elapsed
since midnight.

I am using terminology from Access 2000 help. In the Answer Wizard, "Enter
numbers, text, dates, and times in a spreadsheet". Click on "Tips for
entering numbers, text, dates, and times," then click "Dates and time".
Under the heading "Viewing the serial number behind a date or time" is this
sentence: "Regardless of the format that's used to display a date or time,
a spreadsheet stores all dates as serial numbers and stores all times as
decimal fractions."
I ran across that in the past by accident. I had quite a time trying to
find it again.

I didn't mean to suggest that you would. I was extrapolating e.g.
revealing that a date is stored as a number may tempt someone to
operate on it using mathematical functions rather than temporal
functions.


It makes assumptions about how the data is stored and will continue to
be stored in future releases/different products. I have worked with a
SQL DBMS that stored date/time values as text: I've no idea what <date>
+ 7 would do on that platform...and there's my point. Although not
standard SQL, using DATEADD() is still more implementation-independent
e.g. the storage of date/time could be switched from 'serial number' to
'ticks', 'beats' or whatever but DATEADD() would still be expected to
work because it deals with the temporal data rather than the raw value.

Since I have not worked much with Access data outside of the Microsoft
Office Suite, I have not had to confront issues related to different
platforms. Presumably a calculation wouldn't be stored, but for calculating
intervals from a given date I would use DateAdd if for no other reason than
that there is one function for days, months, and years rather than using one
way of handling days and another for longer intervals.

I don't know what that means. A table is a more of a 'logical' concept
than something 'physical' that has an appearance. I'm often puzzled
when I read here that "editing should not be done in the table itself"
(I don't mean to suggest that you would say such a thing, though).

It is possible to open a table and view the data. That was all I meant.

Because 'Me too!' posts as to be discouraged. I'd never get anything
done if I spent my time replying to everything I saw with which I am
broadly in agreement.

Do I understand that you would recommend changing the system-wide date
format to achieve the mm/dd/yy Short Date format?

 

[BruceM]

Perhaps the most bewildering terminology in Access help is the use of
"spreadsheet". I'm still not sure what that means in the Access world.

This has been an interesting exchange. I will be keeping an eye out for
your future postings in this forum.