Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update Table with VBA in Access

Posted on 2014-09-17
4
Medium Priority
?
378 Views
Last Modified: 2014-09-17
Syntax error in the following VBA Update Statement:

DoCmd.RunSQL "UPDATE tbl_BiWeek" _
& "SET [Paid] = " & "Yes" _
& "WHERE ([PayPeriodStart] = " & Me.lstPayPeriods.Column(2, intRow) & _
"AND [PayPeriodEnd] = " & Me.lstPayPeriods.Column(3, intRow) & ")"

Field [Paid] is a text short text field
Using Access 2013
0
Comment
Question by:hopelessinsalem
  • 2
4 Comments
 
LVL 40

Expert Comment

by:PatHartman
ID: 40328845
The easiest way to resolve errors of this nature is to build the SQL into a string.

Dim strSQL as String

strSQL =  "UPDATE tbl_BiWeek" _
 & "SET [Paid] = " & "Yes" _
 & "WHERE ([PayPeriodStart] = " & Me.lstPayPeriods.Column(2, intRow) & _
 "AND [PayPeriodEnd] = " & Me.lstPayPeriods.Column(3, intRow) & ")"
DoCmd.RunSQL strSQL

That way, if you have an error that isn't immediately obvious, you can print the string to the debug window and copy and paste it into the QBE where you will be able to "see" it and will probably get better error messages.

But, the problem is probably with the Paid field.   String values need to be enclosed in quotes.  So --
& "SET [Paid] = 'Yes'" _
might solve the problem
If PayEnd is a date, you have more syntax errors to come.
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40328852
Your list columns are considered text once they get into the list, so you must format those as dates if the [PayPeriodStart] and [PayPeriodEnd] fields are date fields.

Dim strSQL as string
With me.lstPayPeriods
     strSQL = "UPDATE tbl_BiWeek SET [Paid] = " & chr$(34) & "Yes" & chr$(34) _
                  & "WHERE ([PayPeriodStart] = #" & .Column(2, intRow) & "# " _
                  & "AND [PayPeriodEnd] = #" & .Column(3, intRow) & "#)"
end with
debug.print strSQL
currentdb.execute strsql, dbfailonerror
0
 

Author Closing Comment

by:hopelessinsalem
ID: 40328891
Thank you so much, both of you.   Second response worked well
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40328938
BTW, I use a function for wrapping text and dates.  The function looks like (from memory):
Public Function fnQuotes(QuoteWhat as Variant, _
                         Optional Delimit as String = """") as String

    IF ISNULL(QuoteWhat) THEN 
        fnQuotes = "NULL"
    ELSE
        fnQuotes = Delimit _
                 & Replace(QuoteWhat, Delimit, Delimit & Delimit) _
                 & Delimit
    End If

End Function

Open in new window

If you pass it a NULL, it will return "NULL", so that you can simply pass a variable and use this to insert NULL into a SQL string.  The Delimit feature allows you to determine what you want to wrap your text with.  So for text, I generally use the default, unless I'm formatting a string for a SQL Server pass-thru query, in which case I pass in the single quote.  For dates, you simply pass in the "#" value.  The advantage of this is that it also replaces instances of either the " or ' within the body of the text you are trying to wrap.  With this function, your SQL string would look like:
strSQL = "UPDATE tbl_BiWeek SET [Paid] = " & fnQuotes("Yes") _
       & " WHERE ([PayPeriodStart] = " & fnQuotes(.Column(2, intRow), "# ") _
       & " AND [PayPeriodEnd] = " & fnQuotes(.Column(3, intRow), "#") & ")"

Open in new window

It is probably just me, but I find that easier to read than the other techniques.
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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

772 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