Solved

Update query using VBA in Access

Posted on 2010-11-17
3
593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

740 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