Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Problem with Date type

    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 ?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    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.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Whenever I've wanted different controls for time and date, I have always used separate fields. Just seems to be a whole lot easier.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    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.

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •