Solved

Update query using VBA in Access

Posted on 2010-11-17
3
561 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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now