Results 1 to 4 of 4
  1. #1
    exo is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010

    Convert 5 digit serial date within access

    I am importing a table from a Clarion TPS database which stores several types of data in a "IDVAL" field. The field next to it, "LBLNUM" defines what the data type is. This creates a problem, as IDVAL is a text field, which means all data stored in it (no matter what the type) is then stored as text. This includes date fields. Since they are stored as text, they end up as 5 digit serial dates, similar to how excel handles dates (with each day after 1/1/1900 being +1).

    So I have a union query which derives all the values stored in IDVAL for each unique record. My query which creates my export combines this data with several calculated fields to create my final export.

    Everything looks great on this final export except for my date fields, which are all 5 digit numbers (like 77945).

    I have been searching for a date function that will format this back to a standard date, however I have yet to find anything that works. Is there no way to format a 5 digit serial date back to a standard date within access by using a function? Every answer I find says to redefine the table to a date field, however I don't have this option since the field in question stores a lot more than just dates.

    I suppose in a worst case scenario I could go and manually fix it in the excel export, however considering how often I'll be kicking this report out, I would much rather have it done and handled before I even export the final report.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL

  3. #3
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Knoxville, Tennessee
    Yes I have a situation where the field stores more than dates. YOu could try IIF(isnumeric(val(mynotsodatefield)),cdate(mynotso datefield),mynotsodatefield). this would evaluate if there are just 5 numbers

    ?iif(IsNumeric(val("77945 SomeData")),Cdate(val("77945 SomeData")),"77945 SomeData")

  4. #4
    exo is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    The link orange posted was very helpful. It not only gave a helpful statement, it also pointed out that Clarion stores dates in a different 5 digit format than Office, which means a direct conversion would have been wrong anyways.

    I took the code on the link and modified it to simply run in a query:
    Completion Date: IIf([qryIDs]![Comp_Date] Is Null,"",DateAdd("d",CLng([qryIDs]![Comp_Date]),CDate("12/28/1800")))

    That works perfectly. Thanks!

    edit: This si somewhat similar to what Perceptus wrote. However I didn't have to worry about string length simply because my union query re-orders my table by taking each value type and assigning it to it's own column. So by pointing to that union, I can limit it to only the date fields. I did have to watch out for Null values though.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-12-2015, 01:22 PM
  2. Convert Short text column to date ms access
    By SameerSarswat in forum Access
    Replies: 3
    Last Post: 10-09-2014, 10:42 AM
  3. Replies: 6
    Last Post: 09-20-2013, 01:58 PM
  4. Importing 4 digit field as date
    By tagteam in forum Access
    Replies: 9
    Last Post: 06-13-2013, 02:34 PM
  5. Replies: 1
    Last Post: 09-06-2011, 05:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums