Results 1 to 6 of 6
  1. #1
    stokovich is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    3

    Problem with datetime2 field type after Access 2016 Runtime update to build version 2009

    Hello all.

    We have FE application in MS Access ( Developed on Office 365 Pro Plus ) that is linked to a MS SQL Express DB as BE. After Access Runtime 2016 was updated to version 13231.20262 ( build 2009 ), the datetime2 fields in the tables started to be interpreted as text. This only happens when the field is connected to a declared variable in VBA and used in a form. If we make a new form in Access and link the field through the Access UI, the field is correctly interpreted as date. Before the update there was no problem with this data type and VBA. Everything was working as intended. I hope this is a bug from Microsoft's end, but if someone can elaborate on the issue and give us an advice, it would be great.

    Additionally we saw that now the data type in Access is Date/Time Extended. Before the update it was Date/Time. Maybe this is the issue. VBA does not work correctly with this newly introduced data type still?


    Thank you in advance.



  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,535
    make a query to to break up the text field, into normal Date/time.
    usage in qry: cvt2Date([field])

    Code:
    function cvt2Date(pvVal)
    dim m,d,y
    if isnull(pvVal) then exit function
    
    y = left(pvVal,4)
    m = mid(pvval,5,2)
    d = mid(pvval,7,2)
    
    cvt2Date= m & "/" & d & "/" & y
    end function

  3. #3
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    706
    Do you really need the extra precion given by datetime2? If you don't need the fractional precision up to 100 nano seconds, I advise to use the datetime field type. If you don't need the time registration you could even do with the date format.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,723
    This link from 4 years ago probably explains why (in your case) and what the solution could be

    https://stackoverflow.com/questions/...fields-as-text

  5. #5
    stokovich is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    3
    Quote Originally Posted by NoellaG View Post
    Do you really need the extra precion given by datetime2? If you don't need the fractional precision up to 100 nano seconds, I advise to use the datetime field type. If you don't need the time registration you could even do with the date format.
    You are right. We don't need such precision. "date" would be enough. We will convert data type in SQL. Thank you for the input!

  6. #6
    stokovich is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    3
    Thank you for the input and the code. It will be useful for us.

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

Similar Threads

  1. Replies: 6
    Last Post: 02-04-2018, 09:39 AM
  2. Replies: 5
    Last Post: 09-24-2017, 11:33 AM
  3. Replies: 4
    Last Post: 11-29-2015, 03:51 AM
  4. ms access runtime version for other computers
    By charlesgardner51 in forum Access
    Replies: 1
    Last Post: 09-09-2015, 03:27 PM
  5. Replies: 0
    Last Post: 10-20-2009, 01:10 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 - Senior Forums