help with syntax error in SQL statement

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

I'm getting a syntax error in the following SQL statement.

CurrentDb.Execute "UPDATE tblReceipt SET Matched = 0 WHERE
tblReceipt.ReceiptID = " & [Forms]![frmInvoice_AccountingOnly]![Receipt_ID]
& "'"

I think it has to do with the quotation marks, and I've tried various
combinations on single and double quotes, but I can't get any of them to
work.

Can anyone help me with this?

Thanks in advance.

Paul
 
Try putting a single quote after the = sign, and make sure there is a space
before and after the & sign.

Cheers

Mark
 
Thanks for your suggestion, Mark.

Well, I tried inserting a single quote after the equal sign, and made sure
there is a space before and after the sign, but it now says there's a
missing ), ] or item in the statement.

Any idea what else I might be able to try?

Thanks

Paul


Gassy said:
Try putting a single quote after the = sign, and make sure there is a space
before and after the & sign.

Cheers

Mark

Paul James said:
I'm getting a syntax error in the following SQL statement.

CurrentDb.Execute "UPDATE tblReceipt SET Matched = 0 WHERE
tblReceipt.ReceiptID = " & [Forms]![frmInvoice_AccountingOnly]![Receipt_ID]
& "'"

I think it has to do with the quotation marks, and I've tried various
combinations on single and double quotes, but I can't get any of them to
work.

Can anyone help me with this?

Thanks in advance.

Paul
Click to expand...
Click to expand...
 
I simplified the SQL statement by putting the value of the form control in a
variable.

Here's what I finally used to get it working:

Dim InvoiceNumber As String
If Not IsNull([Forms]![frmInvoice_AccountingOnly]![Receipt_ID]) Then
InvoiceNumber = [Forms]![frmInvoice_AccountingOnly]![Receipt_ID]
CurrentDb.Execute "UPDATE tblReceipt SET Matched = 0 WHERE
tblReceipt.ReceiptID = " & InvoiceNumber & ""
End If

Thanks again for your Reply, Mark.

Paul
 
I'm getting a syntax error in the following SQL statement.

CurrentDb.Execute "UPDATE tblReceipt SET Matched = 0 WHERE
tblReceipt.ReceiptID = " & [Forms]![frmInvoice_AccountingOnly]![Receipt_ID]
& "'"

I think it has to do with the quotation marks, and I've tried various
combinations on single and double quotes, but I can't get any of them to
work.

Can anyone help me with this?

Thanks in advance.

Paul
Click to expand...

Where is this code being run from?
If it is in the [frmInvoice_AccountingOnly] form, then you can
substitue the Me! keyword for forms!frmInvoice_AccountingOnly.
It makes reading the code a lot easier, and saves a lot of errors
caused by miss-spelled names.
tblRecipt.ReceiptID = " & Me![ReceiptID]

If the code is being run from another form or module, then you must
keep the forms!FormName syntax and the form must be open when the
Update is run.

The syntax depends upon what datatype [ReceiptID] is.
If it is a Number Datatype, then:

"Update ....
WHERE tblReceipt.ReceiptID = " &
[Forms]![frmInvoice_AccountingOnly]![Receipt_ID] & ";"

However, if [ReceiptID] is a Text datatype, then use:

"Update ....
WHERE tblReceipt.ReceiptID = '" &
[Forms]![frmInvoice_AccountingOnly]![Receipt_ID] & "':"

for clarity the quotes look like this:
= ' " & [Forms]![frmInvoice_AccountingOnly]![Receipt_ID] & " ' ;"

In either case the statement, as written, should all be on one line.

I would also suggest you use
CurrentDb.Execute "Update ... etc. ... , dbFailOnError
to get an error message if the Update fails.
 
On Thu, 05 Aug 2004 01:45:41 GMT, fredg wrote:

I clicked send a second too quickly.
This line:
"Update ....
WHERE tblReceipt.ReceiptID = '" &
[Forms]![frmInvoice_AccountingOnly]![Receipt_ID] & "':"
Click to expand...
should have a semicolon at the end, not a colon.

[Forms]![frmInvoice_AccountingOnly]![Receipt_ID] & "';"
Where is this code being run from?
If it is in the [frmInvoice_AccountingOnly] form, then you can
substitue the Me! keyword for forms!frmInvoice_AccountingOnly.
It makes reading the code a lot easier, and saves a lot of errors
caused by miss-spelled names.
tblRecipt.ReceiptID = " & Me![ReceiptID]

If the code is being run from another form or module, then you must
keep the forms!FormName syntax and the form must be open when the
Update is run.

The syntax depends upon what datatype [ReceiptID] is.
If it is a Number Datatype, then:

"Update ....
WHERE tblReceipt.ReceiptID = " &
[Forms]![frmInvoice_AccountingOnly]![Receipt_ID] & ";"

However, if [ReceiptID] is a Text datatype, then use:

"Update ....
WHERE tblReceipt.ReceiptID = '" &
[Forms]![frmInvoice_AccountingOnly]![Receipt_ID] & "':"

for clarity the quotes look like this:
= ' " & [Forms]![frmInvoice_AccountingOnly]![Receipt_ID] & " ' ;"

In either case the statement, as written, should all be on one line.

I would also suggest you use
CurrentDb.Execute "Update ... etc. ... , dbFailOnError
to get an error message if the Update fails.
Click to expand...
 
Thanks for such a clear explanation along with the syntax for the two
different datatypes, Fred. This is not only helpful to me in this instance,
but it will also be helpful in some other procedures I need to write.

Yes, I'm calling the procedure from Forms!frmInvoice_AccountingOnly. I
didn't realize you could use Me in an SQL statement. Thanks for that info
as well.

Paul
 
Back
Top