I need a date format with leading zeros.

[Guest]

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.

 

[Jamie Collins]

A date is stored behind the scenes as a serial number. You can format it as
you choose.

With respect, I think your emphasis misses the mark. I understand,
though, you are probably trying to encourage the OP to think beyond the
text they see 'being' the date/time value itself, which is a good
thing.

I consider it immaterial to know which data type the engine uses to
store a date/time value; for me it's more like an 'adds interest'
rather than an 'essential knowledge' issue. Would it matter if the
engine stored the data as multiple integer formats, text, etc as long
as it was DATETIME by the time it left storage?

I would be wary of a solution that relied on a date being stored as a
double float under the covers (I won't mention portability issues, they
aren't popular round here <g>).

Bottom line: if you are working with temporal data then use the
engine's temporal functionality e.g. if you want to know the date next
week then use the DATEADD function rather than relying on DATE() + 7.

Jamie.

--

 

[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.

 

[Jamie Collins]

I agree that mentioning the serial number
probably added little if anything to the op's understanding.

I don't know what you mean "double float under the covers".

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 wonder why you assumed I would recommend using Date() + 7 for next weeks
date (rather than DateAdd).

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.

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.

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.

appearance
of the number in the table

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).

I wonder why you chose to comment on <blah>, but not on the
statement <blah blah>

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.

I appreciate your taking the time to read and comment.

My pleasure ;-)

Jamie.

--

 

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

 

[Jamie Collins]

I am using terminology from Access 2000 help.

Now I see. The Access help certainly does use some 'quirky' terminology
at times, IMO. I get the impression that it is trying to dumb down e.g.
it thinks I might get confused with Excel if it calls it a 'row' so it
plumps for 'record'. I'm fine with people using the term 'field' but I
do feel it would be beneficial if the help adopted the arguably more
'relational' terms. As you've shown, there is an imperative around here
to, not unwisely, conform the 'Microsoft-approved' terminology. I'd
like to think Microsoft are reviewing their documentation to ensure it
is the best it can be, rather than sticking to the same old terms for
historical reasons.

I have not worked much with Access data outside of the Microsoft
Office Suite

It is possible to open a table and view the data.

Hint: are you looking at the actual data in the actual table or are you
looking at a form hosting a data control filled with data that was
retrieved from the table?

Do I understand that you would recommend <blah>

No, I changed the specifics to <blah> to try to avoid any confusion
when making a general point.

Jamie.

--

 

[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.