SQL Syntax 101 Revisted

[Guest]

This is a follow up to a post made on May 24. Thank you to those who
responded to my request for help. However this still has not yet come
together for me.

I am updating table fields with form data. With some help my code is now:

SQL = "UPDATE TCURRENT " & _
"SET CurrentLabRate = " & Forms!FESC!Rate, " & _
"SET CurrentMatInd = " & Forms!FESC!cmbNewMatInd, & ";" & _
"SET CurrCPI = " & Forms!FESC!CboNewCPIInd " & _
"WHERE TCURRENT.SMInvID = " & Forms!FESC![SM Inv ID] & ";"

The fields I am attempting to update are all numeric (currency and double).
The first syntax error I am getting is in the 1st SET line at the comma after
the "Rate" field.

Can someone please advise where my error is?

Thank you

 

[Dirk Goldgar]

This is a follow up to a post made on May 24. Thank you to those who
responded to my request for help. However this still has not yet come
together for me.

I am updating table fields with form data. With some help my code is
now:

SQL = "UPDATE TCURRENT " & _
"SET CurrentLabRate = " & Forms!FESC!Rate, " & _
"SET CurrentMatInd = " & Forms!FESC!cmbNewMatInd, & ";" & _
"SET CurrCPI = " & Forms!FESC!CboNewCPIInd " & _
"WHERE TCURRENT.SMInvID = " & Forms!FESC![SM Inv ID] & ";"

The fields I am attempting to update are all numeric (currency and
double). The first syntax error I am getting is in the 1st SET line
at the comma after the "Rate" field.

Can someone please advise where my error is?

Thank you

You have a couple of them. You shouldn't have multiple SET keywords,
and you mustn't have a semicolon in the middle of the statement. Try
this:

SQL = "UPDATE TCURRENT " & _
"SET CurrentLabRate = " & Forms!FESC!Rate, " & _
"CurrentMatInd = " & Forms!FESC!cmbNewMatInd, " & _
"CurrCPI = " & Forms!FESC!CboNewCPIInd " & _
"WHERE TCURRENT.SMInvID = " & Forms!FESC![SM Inv ID] & ";"

 

[Allen Browne]

The comma needs to be inside the quotes.

To help you see an example of what you need to create, let's mock up a query
with any literal values:

1. Create a new query using the TCurrent table.

2. Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

3. Drag the CurrentLabRate into the grid, and type a number such as 9 into
the Update row under this field.

4. Repeat for the CurrentMatInd and CurrCPI fields, using any number in the
Update row.

5. Drag SMInvID into the grid, and type 99 into the Criteria row under this
field.

6. Switch the query to SQL View (View menu.)

You can now see an example of the string you have to create in VBA. The
numbers will be concatenated into the string from the controls on the FESC
form of course, but that's the target.

After you have the code right, you can verify it by adding this line:
Debug.Print SQL

Now if it still fails, you can open the Immediate Window (Ctrl+G), and see
why the string you build in code does not match what you saw in SQL View of
your mock query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...