Full Version: UPDATE statement not updating 1 field
UtterAccess Forums > Microsoft® Access > Access Forms
Lucky755
This is my code:
CODE
  Dim SQLSNOK1_data As String
SQLSNOK1_data = "UPDATE (SNOK1data) " & _
       "SET [Last] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Last & "', [First] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_First & "'," & _
           "[Title] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Title.Column(1) & "', [SSN] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_SSN & "'," & _
           "[Relationship] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Relationship.Column(1) & "', [DOB] = " & IIf(IsDate(Forms!NewCase!SNOK1_Data.Form!SNOK1_DOB), "#" & Forms!NewCase!SNOK1_Data.Form!SNOK1_DOB & "#", "Null") & "," & _
           "[Address] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Address & "', [City] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_City & "'," & _
           "[State] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_State & "', [Zip] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Zip & "'," & _
           "[Housing] = '" & Forms!NewCase!SNOK1_Data.Form!cmbSNOK1_housing.Column(1) & "', [Home] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Home & "'," & _
           "[Work] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Work & "', [Cell] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Cell & "'," & _
           "[email] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_email & "', " & _
           "[deathgrat] =  " & Forms!NewCase!SNOK1_Data.Form!SNOK1_deathgrat & ", [sgli] =  " & Forms!NewCase!SNOK1_Data.Form!SNOK1_sgli & ", " & _
           "[UPPA] =  " & Forms!NewCase!SNOK1_Data.Form!SNOK1_UPPA & ", [effects] =  " & Forms!NewCase!SNOK1_Data.Form!SNOK1_effects & ", " & _
           "[other] =  " & Forms!NewCase!SNOK1_Data.Form!SNOK1_other & ", [sgli_percent] = " & Nz(Forms!NewCase!SNOK1_Data.Form!SNOK1_sgli_percent.Value, 0) & ", " & _
           "[CACO_Command] = '" & Forms!NewCase!SNOK1_Data.Form!cmbSNOK1_CACO_Command.Column(1) & "', [CACO_Name] = '" & Forms!NewCase!SNOK1_Data.Form!cmbSNOK1_CACO_last.Column(2) & "', " & _
           "[Not_Date] =  " & IIf(IsDate(Forms!NewCase!SNOK1_Data.Form!snok1_not_date), "#" & Forms!NewCase!SNOK1_Data.Form!snok1_not_date & "#", "Null") & ",[Not_By] = '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Not_By & "', " & _
           "[Not_Time] =  " & Nz(Forms!NewCase!SNOK1_Data.Form!snok1_not_time, 0) & "," & _
           "[Time_Not] =  '" & Forms!NewCase!SNOK1_Data.Form!SNOK1_Time_Not & "'" & _
        "WHERE [case_id_SNOK1] =" & Forms!NewCase!CaseID

When I execute it (I have the debugger running), it works fine (no errors), but the Not_time (time of notification) does not update in the table. The field name is correct, the table column name is correct (Not_Time is an actual time and the table column is set as a date/time field). I could change it to text, but I prefer to maintain it as a time.
(BTW, I do realize I have a Not_Time and a Time_Not field. That is not causing my error. Time_Not is a text field)
ANy thoughts?
pbaldy
I think the time field should be surrounded by #, just as the date field is.
Lucky755
tried it, but don't think I had the format right
vtd
Try:
CODE
...
"[Not_Time] =  " & Format(CDate(Nz(Forms!NewCase!SNOK1_Data.Form!snok1_not_time, 0)), "\#hh:nn:ss\#) & "," & _
...
Lucky755
Van-
Thanks. There was one " missing, I think right after the formating.
But, once I put that in, I was all set.
vtd
You're welcome ... Glad you worked it out ...
Yes, I omitted the double-quote after the second hash (#) on re-reading the code I posted.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.