hopelessinsalem
asked on
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
DoCmd.RunSQL "UPDATE tbl_BiWeek" _
& "SET [Paid] = " & "Yes" _
& "WHERE ([PayPeriodStart] = " & Me.lstPayPeriods.Column(2,
"AND [PayPeriodEnd] = " & Me.lstPayPeriods.Column(3,
Field [Paid] is a text short text field
Using Access 2013
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much, both of you. Second response worked well
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
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), "#") & ")"
It is probably just me, but I find that easier to read than the other techniques.
Dim strSQL as String
strSQL = "UPDATE tbl_BiWeek" _
& "SET [Paid] = " & "Yes" _
& "WHERE ([PayPeriodStart] = " & Me.lstPayPeriods.Column(2,
"AND [PayPeriodEnd] = " & Me.lstPayPeriods.Column(3,
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.