Solved

Update query using VBA in Access

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add records to a form to a table 11 40
Search for text in a .txt file 14 44
Trying to open FORM in specific record !! 6 45
Report with several filters - Issue with query? 3 23
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

770 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