Results 1 to 2 of 2
  1. #1
    jackrabbit1962 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    3

    Bug with Datetime in SqlServer

    I am using Access 2010 with a SqlServer Express 2008 with SqlServer 2008 with the SqlServer 2005 Backword Compatibility (BC) package.



    My application was originately developped with an older version, so there are plenty of DateTime fields to store date or time values.

    If I update a date / time values using a command similar to this, MyTime becomes "1900-01-01 11:22:33.000":

    INSERT INTO [MyTable] SET MyTime = '11:22:33' WHERE ...

    However, if I read my record and attempt an update with the same value in a loop of my program, MyTime becomes "1899-12-30 11:22:33.000":

    Dim rst As New ADODB.RecordSet
    rst.Open "SELECT * FROM [MyTable] WHERE ..."
    rst.MoveFirst
    While Not rst.EOF
    rst!MyTime = '11:22:33'
    Wend
    rst.Close

    How can I assign a time field using code assignment, so that the unused date part of a time if always the same value "1900-01-01"

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The first thing I'd try is rst!MyTime = #11:22:33#

    Assuming MyTime is a date/Time field, I haven't tested date insertions this way but in update queries etc Access will interpret your '11:22:33' as a string, not a date/time.

    Another option, put the date as well as the time into the MyTime field. Even in access, if you enter a time (only) in a date/time field there is a date component, you just typically can't see it. If you want to force a specific date you'll have to make that part of your time component like

    rst!mytime = #1/1/1900 11:22:33#

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

Similar Threads

  1. Help with datetime field in subform
    By Delta729 in forum Access
    Replies: 3
    Last Post: 04-07-2015, 04:47 PM
  2. Help with rounding datetime
    By Delta729 in forum Access
    Replies: 10
    Last Post: 04-03-2015, 12:13 PM
  3. Dateserial and datetime
    By webisti in forum Access
    Replies: 1
    Last Post: 06-17-2013, 11:31 AM
  4. Replies: 2
    Last Post: 05-21-2013, 02:25 PM
  5. Replies: 1
    Last Post: 03-25-2011, 10:00 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