Run SQL SUM Cannot update Records (1 Viewer)

Malcolm17

Member
Local time
Today, 16:58
Joined
Jun 11, 2018
Messages
107
Hi,

I am looking to sum some records in one table and paste the total in to a record in another table. I'm struggling a bit with my Run Sql command (I'm not that great at SQL commands in VBA). Initially it didn't seem to work, however I put the SUM part in single speech marks and that seems to have helped, however I am now getting an error saying it cannot update due to a conversion failure.

Please can you have a look at this for me? I have also updated a small mockup of my database so you can see what I am trying to do and also the error to maybe help.

The SQL code I am using is
Code:
DoCmd.RunSQL "UPDATE Diary SET MainBarDrinksRevenue = 'SELECT SUM() Gross FROM ECRSalesData WHERE MGroupCode = 5' WHERE TheDate = Forms.FirstLoad.TheDate"

When you open the database, you should open the form FirstLoad, add todays date to the textbox and click Start Manual Run, this should then generate the error which I cannot understand, however I believe it is an issue with my code.

Thank you
 

Attachments

  • Mockup SUM Posting.accdb
    476 KB · Views: 154

June7

AWF VIP
Local time
Today, 08:58
Joined
Mar 9, 2014
Messages
5,423
An aggregate query cannot be used in an UPDATE action. Aggregate data should not be saved to table (or at least rarely a need to). If it can be calculated for saving it can be calculated when needed.

By enclosing the SQL in apostrophes you have defined a string and the UPDATE is simply trying to save that string (not a number value) into a number field which is of course causing conversion error.
 
Last edited:

Malcolm17

Member
Local time
Today, 16:58
Joined
Jun 11, 2018
Messages
107
Hi June7,

Thanks for that, its a very interesting point which I hadn't thought of like that before, we used to type in the end of day figures and I was looking to calculate them, however of course your way they will be updated in real time over the day too.

Many thanks,

Malcolm
 

Users who are viewing this thread

Top Bottom