Copying SQL from query to VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
George R said:
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
Click to expand...

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.
 
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


George R said:
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]+"
& _
Click to expand...
"[tblDuesPayments]![CollCostsPd]+[tblDuesPayments]![AssessmentPd]+[tblDuesPa
yments]![AttyFeePd]) said:
DoCmd.RunSQL mySQL
Click to expand...
 
Thank you, tina. That worked!

tina said:
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


George R said:
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]+"
& _
Click to expand...
"[tblDuesPayments]![CollCostsPd]+[tblDuesPayments]![AssessmentPd]+[tblDuesPa
yments]![AttyFeePd]) said:
DoCmd.RunSQL mySQL
Click to expand...
Click to expand...
 
you're welcome :)


George R said:
Thank you, tina. That worked!

tina said:
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


George R said:
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
Click to expand...
(([tblDuesPayments]![CurrentYearDuesPd]+[tblDuesPayments]![LastYearDuesPd]+"
& _
"[tblDuesPayments]![PriorYearsDuesPaid]+[tblDuesPayments]![LateFeePd]+"
& _
Click to expand...
"[tblDuesPayments]![CollCostsPd]+[tblDuesPayments]![AssessmentPd]+[tblDuesPa
yments]![AttyFeePd]) said:
DoCmd.RunSQL mySQL
Click to expand...
Click to expand...
Click to expand...
 
Back
Top