Update Table

[Guest]

I'm using the following code to update 2 fields in a table:

Private Sub Form_Unload(Cancel As Integer)
Dim strSQL As String
strSQL = "update [tbl-offsites]" _
& "set releasedDate = #" & Date & "#" _
& "where [Vault No] = " _
& Nz(Me![Vault No]) & ";"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End Sub

It works, but when I add the following "& storagelocation = '27' _" after
"[Vault No]it errors on the CurrentDb.Execute and I don't understand. Could
someone please tell me what I'm doing wrong

 

[John Vinson]

I'm using the following code to update 2 fields in a table:

Private Sub Form_Unload(Cancel As Integer)
Dim strSQL As String
strSQL = "update [tbl-offsites]" _
& "set releasedDate = #" & Date & "#" _
& "where [Vault No] = " _
& Nz(Me![Vault No]) & ";"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End Sub

It works, but when I add the following "& storagelocation = '27' _" after
"[Vault No]it errors on the CurrentDb.Execute and I don't understand. Could
someone please tell me what I'm doing wrong

I think you want that to be

" AND storagelocation = '27' "

rather than enclosing the concatenation operator & inside the string.
You probably need some blanks as well *or you'll get unreadable
strings like
"update [tbl-offsites]set releasedDate = #3/13/2006#where ..."

strSQL = "update [tbl-offsites] " _
& "set releasedDate = #" & Date & "# " _
& "where [Vault No] = " _
& Nz(Me![Vault No]) & " AND storagelocation = '27';"


John W. Vinson[MVP]