Date Question

[paduanow]

I need to store todays date in a table with the following format:

yyyy,mm,dd

Example: 20090914 (09/14/2009 I need this lead zeros, my store 8
digits/char)

Default will always be todays date. Should the field be a date/time field
or a text field with a string conversion routine that puts it on the required
format?

I will never display this date in a form or report.

 

[Jeff Boyce]

Please be aware that Access stores dates (date/time) the way Access wants
to.

I believe you could simply FORMAT a date that Access stored in the manner
you are describing.

Take a look at Access HELP re: the Format() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

.... and to follow on ...

if the date will never be displayed, what value is there in storing it?

To gain you the set of date/time related functions in Access, I'd recommend
that you use the Date/Time data type to store it.

But perhaps the use to which you plan to put this needs to be described so
we can better help...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[paduanow]

Hi Jeff,

In this application, the date will be used to mark when data was transferred
to another database. It will be used as a Date Stamp if you will. The
format of yyyy,mm,dd is a requirment for a State Application. My be sent in
this order. I also need lead "0" Example 02 or 2 digits stored.

Is access capable of this task?

pad

 

[Graham Mandeno]

Hi Pad

If you have a date value, then you can use the Format function to show it in
pretty much any form you like.

Say you have a date/time field called TransferDate with the value
4-Sep-2009.

Then,
Format([TransferDate], "yyyymmdd")
will give:
20090904

 

[paduanow]

Where do I put the Format command?

 

[John W. Vinson]

Where do I put the Format command?

I'd use the Format *function* - not command - in whatever query you use to
export the data to the state's formatted file: you can just put

ExpDate: Format([datefield], "yyyymmdd")

in a vacant Field cell in the query, and then export this query.

Don't confuse data STORAGE with data PRESENTATION. They are different tasks!