Results 1 to 3 of 3
  1. #1
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171

    Problem with date conversion

    I have an excel spread to import as a table. The spreadsheet has a date field in a weird format, YY-MMM as in 18-Jul. I have imported this spreadsheet into a table and the date field into a table field called BogusDate. I need to convert this to the standard date time format for further date calculations. I have parsed an manipulated this to , what I believe, to be a date time format, called NewDate. This is a one time process so I'm want to store this new date in a field in the same record labeled CCExpDate. CCExpDate is defined in the table as a date field. I now cannot get the NewDate field to populate the CCExpDate field. I am testing this in a form using a button to start the process. The code is not pretty but effective.

    Any thoughts would be appreciated.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I need to convert this to the standard date time format for further date calculations
    You cannot without a day value. What does the data look like in the field that needs to be converted, and what would the desired outcome be? Provide some examples. If you enter 2018-06 into a date field, Access will assume the day value is to be 01, so if you don't have that in the source field, it must be text and not a date. Hence the request for examples of what you have versus what you want.
    EDIT - your 2 digit year field will mean that Access will have to decide if 18 is 2018 or the day number, which I presume depends on your regional settings. So you definitely are starting out with bad data.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    I have found the solution I was seeking.
    Thanks for your effort.
    It was matter of parsing to get the month and the two digit year in a text format then concatenating the pieces tp create a string that looks like the standard date then converting it to the date format.

    Thanks again

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

Similar Threads

  1. UPDATE queries problem : type conversion failure
    By Michail_arkov in forum Queries
    Replies: 3
    Last Post: 10-23-2017, 03:32 AM
  2. Report date conversion
    By FJM in forum Access
    Replies: 1
    Last Post: 07-19-2016, 03:10 AM
  3. Conversion Date to Words
    By sanbhau in forum Modules
    Replies: 2
    Last Post: 04-21-2016, 05:39 AM
  4. Date Conversion
    By mslenker in forum Access
    Replies: 1
    Last Post: 01-30-2013, 09:52 AM
  5. Date Conversion
    By mkc80 in forum Access
    Replies: 1
    Last Post: 06-27-2012, 04:04 PM

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