If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I need a date format with leading zeros.
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. "PURVIANCE" wrote in message ... 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? |
#2
|
|||
|
|||
I need a date format with leading zeros.
BruceM wrote: 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. -- |
#3
|
|||
|
|||
I need a date format with leading zeros.
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" wrote in message oups.com... BruceM wrote: 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. -- |
#4
|
|||
|
|||
I need a date format with leading zeros.
BruceM wrote: 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. -- |
#5
|
|||
|
|||
I need a date format with leading zeros.
Replies inline
"Jamie Collins" wrote in message oups.com... BruceM wrote: 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 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 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. 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. 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). It is possible to open a table and view the data. That was all I meant. 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. Do I understand that you would recommend changing the system-wide date format to achieve the mm/dd/yy Short Date format? I appreciate your taking the time to read and comment. My pleasure ;-) Jamie. -- |
#6
|
|||
|
|||
I need a date format with leading zeros.
BruceM wrote: 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. -- |
#7
|
|||
|
|||
I need a date format with leading zeros.
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. "Jamie Collins" wrote in message oups.com... BruceM wrote: 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. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
leading zeros in text format | BigBrook | General Discussion | 5 | December 11th, 2008 08:00 PM |
convert date format | noel | General Discussion | 1 | March 26th, 2006 11:57 PM |
Viewing date data short vs general format | jenhow | General Discussion | 2 | December 23rd, 2005 02:58 PM |
Convert Intiger to 9 Character Text with Leading Zeros | MoonpieHubby | Running & Setting Up Queries | 4 | August 8th, 2005 07:58 PM |
DATE FORMAT in crosstab query | Mary | Running & Setting Up Queries | 2 | February 18th, 2005 03:31 PM |