?
Solved

Update query using VBA in Access

Posted on 2010-11-17
3
Medium Priority
?
612 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Thrawn3000
  • 2
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34161409
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


0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34161417
if you want to suppress the warning message

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.setwarnings false
DoCmd.RunSQL SQLMasterUpdate

docmd.setwarnings true

End Sub

0
 

Author Closing Comment

by:Thrawn3000
ID: 34161455
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Six Sigma Control Plans

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question