Copying SQL from query to VBA

[Guest]

Can anyone tell me what is wrong with the following? I copied the code in
quotes from the SQL view of a MS Access query design and I keep getting a
syntax error. Thank you for your consideration.
George

Dim mySQL As String

mySQL = "UPDATE tblLots RIGHT JOIN tblDuesPayments" & _
"ON tblLots.ACCTNBR = tblDuesPayments.AcctNbr" & _
"SET tblLots.ThisYrPaid =
Nz(tblLots.ThisYrPaid)+Nz(tblDuesPayments.CurrentYearDuesPd)," & _
"tblLots.LastPmtDate =
IIf(tblDuesPayments.PmtDate>Nz(tblLots.LastPmtDate)," & _
"tblDuesPayments.TotalsUpdated = True" & _
"WHERE (((tblDuesPayments.TotalsUpdated)=False)" & _
"AND
(([tblDuesPayments]![CurrentYearDuesPd]+[tblDuesPayments]![LastYearDuesPd]+"
& _
"[tblDuesPayments]![PriorYearsDuesPaid]+[tblDuesPayments]![LateFeePd]+"
& _

"[tblDuesPayments]![CollCostsPd]+[tblDuesPayments]![AssessmentPd]+[tblDuesPayments]![AttyFeePd])<>0));"

DoCmd.RunSQL mySQL

 

[Rick Brandt]

Can anyone tell me what is wrong with the following? I copied the code in
quotes from the SQL view of a MS Access query design and I keep getting a
syntax error. Thank you for your consideration.
George

You need to have at least one space at the end of line before the line
continuation character. Otherwise when the pieces are stuck together the words
butt right up against each other producing an invalid statement.

 

[tina]

you're missing the "spaces" between words in the strings. for example, when
Access concatenates these two strings

"UPDATE tblLots RIGHT JOIN tblDuesPayments" & _
"ON tblLots.ACCTNBR = tblDuesPayments.AcctNbr" & _

it returns
"UPDATE tblLots RIGHT JOIN tblDuesPaymentsON tblLots.ACCTNBR =
tblDuesPayments.AcctNbr"

of course, Access can't make heads or tails of the fragment
"tblDuesPaymentsON"

change the first string to
"UPDATE tblLots RIGHT JOIN tblDuesPayments " & _
(note the space before the closing quote)

you need to examine each string in the code, and add a space before the
closing quote, *where it is appropriate*.

hth

Can anyone tell me what is wrong with the following? I copied the code in
quotes from the SQL view of a MS Access query design and I keep getting a
syntax error. Thank you for your consideration.
George

Dim mySQL As String

mySQL = "UPDATE tblLots RIGHT JOIN tblDuesPayments" & _
"ON tblLots.ACCTNBR = tblDuesPayments.AcctNbr" & _
"SET tblLots.ThisYrPaid =
Nz(tblLots.ThisYrPaid)+Nz(tblDuesPayments.CurrentYearDuesPd)," & _
"tblLots.LastPmtDate =
IIf(tblDuesPayments.PmtDate>Nz(tblLots.LastPmtDate)," & _
"tblDuesPayments.TotalsUpdated = True" & _
"WHERE (((tblDuesPayments.TotalsUpdated)=False)" & _
"AND
(([tblDuesPayments]![CurrentYearDuesPd]+[tblDuesPayments]![LastYearDuesPd]+"
& _
"[tblDuesPayments]![PriorYearsDuesPaid]+[tblDuesPayments]![LateFeePd]+"
& _

"[tblDuesPayments]![CollCostsPd]+[tblDuesPayments]![AssessmentPd]+[tblDuesPa

DoCmd.RunSQL mySQL

 

[Guest]

Thank you, tina. That worked!

you're missing the "spaces" between words in the strings. for example, when
Access concatenates these two strings

"UPDATE tblLots RIGHT JOIN tblDuesPayments" & _
"ON tblLots.ACCTNBR = tblDuesPayments.AcctNbr" & _

it returns
"UPDATE tblLots RIGHT JOIN tblDuesPaymentsON tblLots.ACCTNBR =
tblDuesPayments.AcctNbr"

of course, Access can't make heads or tails of the fragment
"tblDuesPaymentsON"

change the first string to
"UPDATE tblLots RIGHT JOIN tblDuesPayments " & _
(note the space before the closing quote)

you need to examine each string in the code, and add a space before the
closing quote, *where it is appropriate*.

hth

Can anyone tell me what is wrong with the following? I copied the code in
quotes from the SQL view of a MS Access query design and I keep getting a
syntax error. Thank you for your consideration.
George

Dim mySQL As String

mySQL = "UPDATE tblLots RIGHT JOIN tblDuesPayments" & _
"ON tblLots.ACCTNBR = tblDuesPayments.AcctNbr" & _
"SET tblLots.ThisYrPaid =
Nz(tblLots.ThisYrPaid)+Nz(tblDuesPayments.CurrentYearDuesPd)," & _
"tblLots.LastPmtDate =
IIf(tblDuesPayments.PmtDate>Nz(tblLots.LastPmtDate)," & _
"tblDuesPayments.TotalsUpdated = True" & _
"WHERE (((tblDuesPayments.TotalsUpdated)=False)" & _
"AND
(([tblDuesPayments]![CurrentYearDuesPd]+[tblDuesPayments]![LastYearDuesPd]+"
& _
"[tblDuesPayments]![PriorYearsDuesPaid]+[tblDuesPayments]![LateFeePd]+"
& _

"[tblDuesPayments]![CollCostsPd]+[tblDuesPayments]![AssessmentPd]+[tblDuesPa

DoCmd.RunSQL mySQL

 

[Guest]

Thank you Rick. Adding the space inside the quote fixed the problem!

 

[tina]

you're welcome

Thank you, tina. That worked!

you're missing the "spaces" between words in the strings. for example, when
Access concatenates these two strings

"UPDATE tblLots RIGHT JOIN tblDuesPayments" & _
"ON tblLots.ACCTNBR = tblDuesPayments.AcctNbr" & _

it returns
"UPDATE tblLots RIGHT JOIN tblDuesPaymentsON tblLots.ACCTNBR =
tblDuesPayments.AcctNbr"

of course, Access can't make heads or tails of the fragment
"tblDuesPaymentsON"

change the first string to
"UPDATE tblLots RIGHT JOIN tblDuesPayments " & _
(note the space before the closing quote)

you need to examine each string in the code, and add a space before the
closing quote, *where it is appropriate*.

hth

Can anyone tell me what is wrong with the following? I copied the code in
quotes from the SQL view of a MS Access query design and I keep getting a
syntax error. Thank you for your consideration.
George

Dim mySQL As String

mySQL = "UPDATE tblLots RIGHT JOIN tblDuesPayments" & _
"ON tblLots.ACCTNBR = tblDuesPayments.AcctNbr" & _
"SET tblLots.ThisYrPaid =
Nz(tblLots.ThisYrPaid)+Nz(tblDuesPayments.CurrentYearDuesPd)," & _
"tblLots.LastPmtDate =
IIf(tblDuesPayments.PmtDate>Nz(tblLots.LastPmtDate)," & _
"tblDuesPayments.TotalsUpdated = True" & _
"WHERE (((tblDuesPayments.TotalsUpdated)=False)" & _
"AND

(([tblDuesPayments]![CurrentYearDuesPd]+[tblDuesPayments]![LastYearDuesPd]+"

& _
"[tblDuesPayments]![PriorYearsDuesPaid]+[tblDuesPayments]![LateFeePd]+"
& _

"[tblDuesPayments]![CollCostsPd]+[tblDuesPayments]![AssessmentPd]+[tblDuesPa

DoCmd.RunSQL mySQL