SQL Update Statement

Hi,

I'm trying to update a table that will be used in other queries; using data that the user has selected on a combo box form.

I don't get an error at all but when I open the table, it is blank.

Below is my code, can anyone spot where I am going wrong?
.................................................................
Private Sub CmdOK_Click()

DoCmd.SetWarnings False


If IsNull(cbovaldate) Or IsNull(cboNZRG0date) Or IsNull(cboNBDate) Or IsNull(cboBOYStart) Then
    MsgBox "Valuation dates may not be blank!", vbExclamation + vbOKOnly
    Exit Sub
    End If
   
   

DoCmd.RunSQL "UPDATE zzvaldate SET zzvaldate.Valuation_Date = " & Forms!Update_Valdate!cbovaldate & " ;"

DoCmd.RunSQL "Update zzvaldate set zzvaldate.Valuation_Date_yyyymmdd = " & Forms!Update_Valdate!cboNZRG0date & " ; "

DoCmd.RunSQL "Update zzvaldate set zzvaldate.NB_start_date = " & Forms!Update_Valdate!cboNBDate & " ; "

DoCmd.RunSQL "Update zzvaldate set zzvaldate.BOY_Date = " & Forms!Update_Valdate!cboBOYDate & " ; "

MsgBox "Valuation dates have been updated", vbOKOnly

DoCmd.Close acForm, "Update_Valdate"

DoCmd.SetWarnings True


End Sub
DB_newbie_NZAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
if Valuation_Date field is Date/time Data type, use this

DoCmd.RunSQL "UPDATE zzvaldate SET zzvaldate.Valuation_Date = #" & Forms!Update_Valdate!cbovaldate & "# ;"

if Valuation_Date field is Text Data type, use this

DoCmd.RunSQL "UPDATE zzvaldate SET zzvaldate.Valuation_Date = '" & Forms!Update_Valdate!cbovaldate & "'"
0
 
DB_newbie_NZAuthor Commented:
Thanks capricorn1, I've tried that but it still doesn't work.

The dates in the table are date/time format except for one which is long integer because it is in yyyymmdd format.

The combo box is taking the possbile valuation dates from a table which in turn has formates that match the table to be updated.

I have several of these forms and none of the update queries are working, including the one where I enter text into a text box.
0
 
DB_newbie_NZAuthor Commented:
Capricorn1, your solution works, thanks a mill! The table being updated was null but when i put in dummy dates in there, the table updated.

So I guess  I must specify to update even when the table is null?

Ok, so now I've updated my dates successfully but say next time I want to open the Update_date form just to make sure my valuation dates are correct. Do i have to change the combo boxes to bound so that data entered will be saved in the combo box till I update it?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.