Hello again, Colleagues All,
As I understand it, dates are actually stored as numerical values which represent the usual DMY (or MDY in USA) components and also the HMS components and that the presentation to the viewer or to the code is handled by various formatting patterns.
I have a situation where I wish to use the full date feature (down to seconds accuracy) as part of a key, so I declare the format of a field TransactionDate in the table specification as General Date (dd/mm/yyyy hh:mm:ss). In the actual application, I would like to present the DMY and the HMS components in separate controls on a form. However, when I look directly at the table, the value I see (derived from the system date) is in dd/mm/yyyy format. On the form, I have two fields TransactionDate and TransactionTime, both of which point to the same field TransactionDate, but the first has the format Short Date, while the second has the format Medium Time. Both fields display correctly, except that the TransactionTime field always shows 12:00AM, i.e. the default value, regardless of the system date at the time the data was captured. I have tried other formulations for the time, such as =Time(TransactionDate), but I get the #name error flag. The evidence suggests that the database field only contains DMY components.
Can someone please tell me what is going on, here ?
Access does indeed store the date and time together as a double precision number. I'm wondering how you're capturing the TransactionDate field for the table; are you using Date() or Now()? If you're using Now() to get the current date and time the transaction is completed, then you should be able to put two boxes on your form, and use format() to show date in one and time in the other.
Thanks for your advice, guys. The now() function worked perfectly, Nick. Clearly, Date and Time are really discriminators to access the DMY and the HMS components of the complete date. Funnily enough, Access VBA Help does not bring up this point at all. I quite agree with you StarTrekker. The complete date (ex Now()) is used to provide a comprehensive key component for a group of charges which are added together and paid for in one session by a customer. The Date and Time stuff was only to present the date and time components for viewing. I did this because I wanted to keep as close to the old system I am replacing, being the sort of developer/administrator who always treats his user community with respect (sometimes excessively so, I suspect). However, on pondering your remark for a few moments, I thought that I should be true to myself and retain the systematic design that I had developed, so I have scrapped the time control, widened out the date control to dsiplay all of its components and labelled it Date/Time. After all, it is not really disrepectful to expect a little intelligence from the users, is it.
Thanks for the useful bit of information, garethdart. Actually, I had created a conversion function myself on the same lines as that recommended. However, there is a useful conceptual point to be made here. An SQL statement is an instruction to an external program (the Jet engine, or whatever) and needs to be in a format it can understand and the only format that fits is US as the engine is unaware of regional variations. However, other programs such as Access ARE aware of the regional date formats and are able to make the necessary conversions behind the scenes before talking to the Jet engine.