Update Table with VBA in Access

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
hopelessinsalemAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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
Dale FyeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hopelessinsalemAuthor Commented:
Thank you so much, both of you.   Second response worked well
0
Dale FyeCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.