Solved

Update query using VBA in Access

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Suggested Courses
Course of the Month8 days, 21 hours left to enroll

617 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