none
Why wont date field in linking table update? RRS feed

  • Question

  • I've been grinding on this off and on for a day. Maybe someone will show me what I am doing wrong.

    For each record displayed in a Sub Form I am trying to update a number of fields... including a date type field. Everything updates except for the Date Type field.

    The code runs when an Update Button on the Main Form is clicked.

    This UPDATE query is in a loop that iterates through records in a linking table and updates each record that matches the WHERE criteria.

    For purposes of discussion I have attempted to simplify the Update query to only update the date field.

    Below are 3 attempts to trouble shoot this and get it to work. Obviously I'm missing something...

    'Attempt 1: 'similar to actual code where the date is in a variable

    Dim theDate As Date
    theDate = Now()

    CurrentDb.Execute "UPDATE[tLinking]" & _
    "SET itemDate = " & theDate & _
    " WHERE (((itemNumber )= " & CurrentItemNumber & ") " & _
    " AND ((idOrder)= " & idOrderA & "))

    'Attempt 2: 'try DateValue

    CurrentDb.Execute "UPDATE[tLinking]" & _
    "SET itemDate = " & DateValue(theDate) & _
    " WHERE (((itemNumber )= " & CurrentItemNumber & ") " & _
    " AND ((idOrder)= " & idOrderA & "))

    'Attempt 3: ' try simplest case using Now()
    CurrentDb.Execute "UPDATE[tLinking]" & _
    "SET itemDate = " & Now() & _
    " WHERE (((itemNumber )= " & CurrentItemNumber & ") " & _
    " AND ((idOrder)= " & idOrderA & "))

    None of these work properly.

    Thanks for any help in getting this to work!

    Tuesday, December 4, 2012 12:20 PM

Answers

  • Yes, you've missed # around the date. Also remember to use US date format (mm/dd/yyyy) for passing a date value to SQL statement. Finally, this should work:

    CurrentDb.Execute "UPDATE[tLinking]" & _
    "SET itemDate = #" & Format(Now(), "mm\/dd\/yyyy") & _
    "# WHERE (((itemNumber )= " & CurrentItemNumber & ") " & _
    " AND ((idOrder)= " & idOrderA & ")"

    If the time part is also valuable for you, then use

    Format(Now(), "mm\/dd\/yyyy hh\:nn\:ss")


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Tuesday, December 4, 2012 12:40 PM
  • Try

    CurrentDb.Execute "UPDATE [tLinking] " & _
      " SET [itemDate] = Date() " & _
      " WHERE ([itemNumber]= " & CurrentItemNumber & ") " & _
      "   AND ([idOrder]= " & idOrderA & ")"

    assuming that both Fields [itemNumber] and [idOrder] are numeric Fields.


    Van Dinh

    Wednesday, December 5, 2012 7:56 AM

All replies

  • Yes, you've missed # around the date. Also remember to use US date format (mm/dd/yyyy) for passing a date value to SQL statement. Finally, this should work:

    CurrentDb.Execute "UPDATE[tLinking]" & _
    "SET itemDate = #" & Format(Now(), "mm\/dd\/yyyy") & _
    "# WHERE (((itemNumber )= " & CurrentItemNumber & ") " & _
    " AND ((idOrder)= " & idOrderA & ")"

    If the time part is also valuable for you, then use

    Format(Now(), "mm\/dd\/yyyy hh\:nn\:ss")


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Tuesday, December 4, 2012 12:40 PM
  • Try

    CurrentDb.Execute "UPDATE [tLinking] " & _
      " SET [itemDate] = Date() " & _
      " WHERE ([itemNumber]= " & CurrentItemNumber & ") " & _
      "   AND ([idOrder]= " & idOrderA & ")"

    assuming that both Fields [itemNumber] and [idOrder] are numeric Fields.


    Van Dinh

    Wednesday, December 5, 2012 7:56 AM