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
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
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