Results 1 to 5 of 5
  1. #1
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47

    Converting DateTime to Date Results in Completely Wrong Date

    This code is intended to check if there's already samples on a given day. If so, it should perform the UPDATE query; otherwise it performs the INSERT query. This part, it does fine. However, the dates that appear in the table are completely wrong- i.e. today's date becomes 12/30/1899. There must be a formatting issue, but I've tried every iteration I'm aware of without luck. The source field (me.logindatestamp) is a text box in general date format with default value of Now().



    Code:
    If IsNull(DLookup("[ID]", "[tblDailyQCSamples]", "[CountDate] =" & DateValue(Me.logInDateStamp))) = True Then
        DoCmd.RunSQL "INSERT INTO tblDailyQCSamples ( Count, CountDate) SELECT " & Me.noSamples & ", " & DateValue(Me.logInDateStamp) & ";"
    Else
        DoCmd.RunSQL "UPDATE tblDailyQCSamples SET count = " & DLookup("[count]", "[tblDailyQCSamples]", "[CountDate] =" & DateValue(Me.logInDateStamp)) + Me.noSamples & " WHERE countdate = " & Format(Me.logInDateStamp, "short date") & ";"
    End If
    Any help would be much appreciated!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    That date means the field has time but no date. Basically the date is zero.
    you should keep both together, date/time.
    you can always concat date to help it fill.... [date] & " " & [time]

  3. #3
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47
    Quote Originally Posted by ranman256 View Post
    That date means the field has time but no date. Basically the date is zero.
    you should keep both together, date/time.
    you can always concat date to help it fill.... [date] & " " & [time]
    I appreciate the response, but I'm not quite sure what to do with that advice.

    The exact value of me.logindatestamp in this case was:
    4/26/2017 5:33:35 PM

    This was visible within the debugger, so it's definitely pulling that value correctly. Not sure what's making that into a date of 0.

    I tried running my code as simply:

    Code:
    If IsNull(DLookup("[ID]", "[tblDailyQCSamples]", "[CountDate] =" & DateValue(Me.logInDateStamp))) = True Then
        DoCmd.RunSQL "INSERT INTO tblDailyQCSamples ( Count, CountDate) SELECT " & Me.noSamples & ", " & Me.logInDateStamp & ";"
    Else
        DoCmd.RunSQL "UPDATE tblDailyQCSamples SET count = " & DLookup("[count]", "[tblDailyQCSamples]", "[CountDate] =" & Me.logInDateStamp) + Me.noSamples & " WHERE countdate = " & Me.logInDateStamp & ";"
    End If
    However, i get Syntax Error (missing operator) in query expression '[CountDate] =4/26/2017 5:33:35 PM'. I assume that has to do with the time at the end of that value, which it doesn't know what to do with.

    So i tried replacing me.logindatestamp with Format(me.logindatestamp, "Short Time") - no dice, this gets me the 12/30/1899 date again.

    So the only thought I have left is the incredibly sloppy:

    Code:
    DatePart("m", Me.logInDateStamp) & "/" & DatePart("d", Me.logInDateStamp) & "/" & DatePart("y", Me.logInDateStamp)
    Which, unfortunately, gets me Syntax error in UPDATE statement... And we've already tried DateValue(), leaving me with no ideas left.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Date fields must be surrounded by # - as in
    WHERE countdate = #" & Me.logInDateStamp & "#;"
    if countdate is defined as a date/time field.

  5. #5
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47
    Quote Originally Posted by aytee111 View Post
    Date fields must be surrounded by # - as in
    WHERE countdate = #" & Me.logInDateStamp & "#;"
    if countdate is defined as a date/time field.
    Like a charm. Working perfectly now.

    I did try a version with # surrounding once before, but apparently I made some other mistake in that code, because it didn't work at the time. Many thanks!

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

Similar Threads

  1. How to extract date from a datetime text field?
    By stalk in forum Programming
    Replies: 5
    Last Post: 01-18-2017, 12:06 PM
  2. convert a date into a dateTime value
    By JeroenMioch in forum Access
    Replies: 10
    Last Post: 12-13-2016, 06:54 PM
  3. Replies: 2
    Last Post: 07-20-2015, 07:51 PM
  4. Replies: 7
    Last Post: 12-26-2014, 12:17 PM
  5. Get only date from a datetime in access
    By scorpion99 in forum Queries
    Replies: 4
    Last Post: 11-30-2013, 09:43 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