asked on

Update query using VBA in Access

Hi experts
I'm tryting to update a filed [FileDate] within a table (tblSalesReport934LClean) where [FileDate]
is null to a variable that holds a date.
I get the error "Syntax error in update statement"

please help

Sub updatetest()



Dim SQLMasterUpdate As String
Dim varDate As Date

varDate = #12/29/2010#

SQLMasterUpdate = "UPDATE tblSalesReport934LClean SET " & _
                "tblSalesReport934LClean.[FileDate] = " & varDate & ", " & _
                "WHERE tblSalesReport934LClean.[FileDate] = " & Null & ";"


DoCmd.RunSQL SQLMasterUpdate



End Sub

Open in new window

Rey Obrero (Capricorn1)

try this

Sub updatetest()

Dim SQLMasterUpdate As String
Dim varDate As Date

varDate = #12/29/2010#

SQLMasterUpdate = "UPDATE tblSalesReport934LClean SET" & _
" tblSalesReport934LClean.[FileDate] = #" & varDate & "#" & _
" WHERE tblSalesReport934LClean.[FileDate] is Null"

DoCmd.RunSQL SQLMasterUpdate

End Sub

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thrawn3000

ASKER

Thanks Capricorn I managed to work it out, but your answer does the same thing thanks very much, my code is below

Sub updatetest()

Dim SQLMasterUpdate As String
Dim varDate As Date

varDate = "25/12/2010"

SQLMasterUpdate = "UPDATE tblSalesReport934LClean SET " & _
"tblSalesReport934LClean.[FileDate] =#" & CDate(varDate) & "# " & _
"WHERE (((tblSalesReport934LClean.FileDate)Is null));"

DoCmd.RunSQL SQLMasterUpdate

End Sub