help with syntax error in SQL statement

[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

 

[Guest]

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

Cheers

Mark

 

[Paul James]

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

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

Cheers

Mark

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

 

[Paul James]

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

 

[fredg]

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

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.

 

[fredg]

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] & "':"

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.

 

[Paul James]

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