Using Update to update an Access field 
Author Message
 Using Update to update an Access field

Hi,

I am trying to update a field in an Access 2000 db using
VB6.  (This will serve only as temporary db)

The field is a running total calculated by consecutively
adding the qtyshp field.  But I keep getting the error
message:

"Syntax error (missing operator) in query
expression '(SELECT ....'

Here is the code I am trying to use:

' *** this code works perfectly in a recordset
mSelect4 = "(SELECT SUM(t2.qtyshp) FROM TempThree AS t2
WHERE t2.id <= t1.id) AS Total FROM TempThree AS t1 "
' *** end

mSelect2 = "UPDATE TempThree " _
       & "SET FirstRec = " _
       & " " & mSelect4 & " "

Is there a better way to write this, or is there a
fundamental problem here?  So close!

Thanks,

Steve



Thu, 15 Jul 2004 08:17:37 GMT  
 Using Update to update an Access field
Steve,

It appears there is a slight syntax error in your UPDATE statement. The 'single
quote' and some spacing is missing.

Try to modify your statement to look as follows:
mSelect2 = " UPDATE TempThree "  _
       & "SET FirstRec  =  ' "  _
       & mSelect4 & "';"

As usual pay particular attention to spaces(l suggest you cut and paste the
above code)

l hope this helps

Regards

Zee

Quote:
>Hi,

>I am trying to update a field in an Access 2000 db using
>VB6.  (This will serve only as temporary db)

>The field is a running total calculated by consecutively
>adding the qtyshp field.  But I keep getting the error
>message:

>"Syntax error (missing operator) in query
>expression '(SELECT ....'

>Here is the code I am trying to use:

>' *** this code works perfectly in a recordset
>mSelect4 = "(SELECT SUM(t2.qtyshp) FROM TempThree AS t2
>WHERE t2.id <= t1.id) AS Total FROM TempThree AS t1 "
>' *** end

>mSelect2 = "UPDATE TempThree " _
>       & "SET FirstRec = " _
>       & " " & mSelect4 & " "

>Is there a better way to write this, or is there a
>fundamental problem here?  So close!

>Thanks,

>Steve



Fri, 16 Jul 2004 01:48:49 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Using--After Update-- to update field in table.

2. 2nd SQL Server update after update text field corrupts row - using RDO

3. Updating data to a memo field using SQL UPDATE

4. How to update datetime field with NULL using ADO update

5. inserting character in an access field by using an update query

6. Using MS Access to populate fields in Project - UPDATE

7. Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures

8. Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures

9. ADO Update (using VB) of Memo fields in Access 97 DB

10. Newbie Update field in DB using AutoNumber field

11. Newbie Update field in DB using AutoNumber field

12. Using Variables for Field Names using .AddNew and .Update

 

 
Powered by phpBB® Forum Software