If one of the numbers used in the calculation changes the saved and calculated values won’t match.
An example is in bookkeeping applications where it is only safe to save invoice totals until a month end close is performed and the invoices can no longer be changed.
Thank you for the answer. Now, if I may ask another that pertains to this topic: I understand that table and field names can cause problems. But, a lot of my tables and field names have spaces within. I use a bunch of macros to open forms and reports, both basic and python. I don’t have any problems with these macros. Only now have I run into this problem: ie the use of spaces, or possibly because of incorrectly written calculated subqueries within a macro. Being untrained makes me feel that this only might be the problem, and that it is possible another problem is lurking within my syntax. The macro I used above, and later coupled with the SQL you helped with does not work. For brief’s sake: I use calculated numbers to place in a table that I use for a graph in a report. I am sure there is a better way, but this is the way I found. The macro would be assigned to a button and is to perform those calculations and place them into the table.
BTW, I’ve put msgbox strsql and it shows me the complete query in a box then shows the error msg box saying Syntax error. But, if I get the syntax correct as I can, then it gives me an error No Valid Table in the msgbox. I will run those again and place them in the post.
My question is: Why would spaces cause this type of problem when it doesn’t with other macros ( I know it may be a dumb question) and is it possible I just have my macro written incorrectly? Thank for any type of answer.
Spaces are not a problem as long as the name is in double-quotes.
@TNBuilder,
I gave instructions on testing the SQL but as there has been no feedback don’t know the outcome.
.
I will not assist anyone in breaching the principles of normalisation.
.
the basic code in your initial post contains 4 errors.
only you have access to your revamped code if you do not wish to share it then how can anyone provide objective feedback.
Okay, I appreciate your info. There may be errors in my coding, I am totally willing to admit that. I will place what I have below. I have no problem sharing my files, was just unsure how much y’all wanted.
Your description of the bookkeeping example is exactly what I am trying to achieve. Daily or intermittently updated calculations from how I keep track of electricity is placed into a table, then is no longer calculated after the month’s end. I would create a new macro to deal with the new month.
Please excuse any ignorance: My database is embedded HSQLDB . My database name us New.odb but I saw somewhere to use Bibliography.
Sub UpdateCost
Dim oContext As Object
Dim oDatabase As Object
Dim oConnection As Object
Dim oStatement As Object
Dim sSQL As String
' Get the database context
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
' Replace "DB_NAME" with your actual database name
oDatabase = oContext.getByName("Bibliography")
' Connect to the database (you may need to provide username and password)
oConnection = oDatabase.getConnection("", "")
' Create the SQL statement
sSQL = "UPDATE ""Electricity Monthly Totals"" SET ""Cost"" = " & _
"(SELECT " & _
"SUM(""NoUnits"" * ""Power"" * ""OpDays"" * 24 / 1000 * 0.09983)" & _
"+" & _
"(" & _
"SELECT SUM(""Cost"")" & _
"FROM ""Expenses Table""" & _
"WHERE ""ExpCatID"" = 8" & _
"AND ""AcctID"" BETWEEN 4999000 AND 4999999" & _
"AND ""Date"" BETWEEN '2024-05-01' AND '2024-05-31'" & _
"AND ""ConJobID"" in(13,14)" & _
")" & _
"FROM" & _
"""Elec Est Table""" & _
"WHERE" & _
"""Finish Date"" BETWEEN '2024-04-16' AND '2024-05-15'" & _
"AND" & _
"""ElecTotalID"" = 8) " & _
"""Where ""Month"" = 'May24'"
msgbox (sSQL)
' Execute the SQL query
oStatement = oConnection.createStatement()
oStatement.executeUpdate(sSQL)
I repeat, did you test the SQL according to my instructions?
did it execute successfully? it’s vital you confirm that it did.
.
the macro code you posted in post #15 25 Apr 20:56 contains several errors.
.
a well constructed, normalised database can easily calculate and display the required data without the need for any of this macro rubbish.
.
I do not have access to your database, am unable to test anything and without any meaningful feedback.
if the SQL does function as expected and your database is registered then try this:
Sub UpdateCost
Dim oContext As Object
Dim oDatabase As Object
Dim oConnection As Object
Dim oStatement As Object
Dim sSQL As String
' Get the database context
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
' Replace "DB_NAME" with your actual database name
oDatabase = oContext.getByName("New")
' Connect to the database (you may need to provide username and password)
oConnection = oDatabase.getConnection("", "")
' Create the SQL statement
sSQL = "UPDATE ""Electricity Monthly Totals"" SET ""Cost"" = " & _
"(" & _
"SELECT " & _
"SUM(""NoUnits"" * ""Power"" * ""OpDays"" * 24 / 1000 * 0.09983)" & _
"+" & _
"(" & _
"SELECT SUM(""Cost"") " & _
"FROM ""Expenses Table"" " & _
"WHERE ""ExpCatID"" = 8 " & _
"AND ""AcctID"" BETWEEN 4999000 AND 4999999 " & _
"AND ""Date"" BETWEEN '2024-05-01' AND '2024-05-31' " & _
"AND ""ConJobID"" in(13,14)" & _
") " & _
"FROM " & _
"""Elec Est Table"" " & _
"WHERE " & _
"""Finish Date"" BETWEEN '2024-04-16' AND '2024-05-15' " & _
"AND " & _
"""ElecTotalID"" = 8 " & _
"AND " & _
"""Month"" = 'May24'" & _
")"
' msgbox (sSQL)
' Execute the SQL query
oStatement = oConnection.createStatement()
oStatement.executeUpdate(sSQL)
End SubHi cpb, Yes, after a few modifications to get my prior syntax errors and make the tables right, the sql given to me on 4/24 @ 9:09 works correctly. Thank you, this is a condensed version of what I had. This is how I made it work:
SELECT
SUM("NoUnits" * "Power" * "OpDays" * 24 / 1000 * 0.09983)
+
(
SELECT SUM("Cost")
FROM "Expenses Table"
WHERE "ExpCatID" = 8
AND "AcctID" BETWEEN 4999000 AND 4999999
AND "Date" BETWEEN '2024-04-01' AND '2024-04-30'
AND "ConJobID" in(13,14)
)
FROM
"Elec Est Table"
WHERE
"FinishDate" BETWEEN '2024-03-16' AND '2024-04-15'
However, I could not make this work in the UPDATE statement in the macro. I get syntax errors, so I know I am messing up somewhere. It looks like this:
Sub UpdateCost
Dim oContext As Object
Dim oDatabase As Object
Dim oConnection As Object
Dim oStatement As Object
Dim sSQL As String
' Get the database context
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
' Replace "DB_NAME" with your actual database name
oDatabase = oContext.getByName("New")
' Connect to the database (you may need to provide username and password)
oConnection = oDatabase.getConnection("", "")
' Create the SQL statement
sSQL ="Update ""Electricity Monthly Totals""" & _
"Set ""Cost"" = " & _
"(SELECT " & _
"SUM(""NoUnits"" * ""Power"" * ""OpDays"" * 24 / 1000 * 0.09983)" & _
"+" & _
"(" & _
"SELECT SUM(""Cost"")" & _
"FROM ""Expenses Table""" & _
"WHERE ""ExpCatID"" = 8" & _
"AND ""AcctID"" BETWEEN 4999000 AND 4999999" & _
"AND ""Date"" BETWEEN '2024-04-01' AND '2024-04-30'" & _
"AND ""ConJobID"" in(13,14)" & _
")" & _
"FROM" & _
"""Elec Est Table""" & _
"""WHERE""" & _
"""FinishDate"" BETWEEN '2024-03-16' AND '2024-04-15' ) AS ""Apr 2024 Total Bill""" & _
"FROM ""Elec Est Table"" WHERE ""FinishDate"" BETWEEN '2024-03-16' AND '2024-04-15' )" & _
"Where ""ElecTotalID"" = '8'"
' msgbox (sSQL)
' Execute the SQL query
oStatement = oConnection.createStatement()
oStatement.executeUpdate(sSQL)
End Sub
Yet, I was able to get to a different error message with the macro code using my longer version of sql, and assuming that I corrected syntax errors. The error message stated in a Message Box that no valid table:
BASIC runtime error.
An exception occurred
Type: com.sun.star.sdbc.SQLException
Message: The query cannot be executed. It contains no valid table..
So, I think that I am messing up with syntax up to a point , then I get that correct, then I get this last message about invalid tables. This is what started me thinking that spaces in names were causing problems, but @Villeroy said that double quotes should work earlier in the thread. So, I am not sure what to think.
I can use your sql, or mine, to perform the SELECT statement and acquire the correct total. I can also use SQL–TOOLS and perform the update statement manually there. And it works too. My UPDATE statement looks like this:
Update "Electricity Monthly Totals"
Set "Cost" =
(SELECT SUM( ( "NoUnits" * "Power" * ( ( "OpDays" * 24 ) / 1000 ) ) * .09983 ) +
( SELECT SUM( "Cost" ) FROM "Expenses Table" WHERE "ExpCatID" = '8' AND ( "AcctID" BETWEEN '4999000' AND '4999999' ) AND ( "Date" BETWEEN '2024-05-01' AND '2024-05-31' ) AND "ConJobID" = '14' ) +
( SELECT SUM( "Cost" ) FROM "Expenses Table" WHERE "ExpCatID" = '8' AND ( "AcctID" BETWEEN '4999000' AND '4999999' ) AND ( "Date" BETWEEN '2024-05-01' AND '2024-05-31' ) AND "ConJobID" = '13' ) AS "May 2024 Total Bill"
FROM "Elec Est Table" WHERE "FinishDate" BETWEEN '2024-04-16' AND '2024-05-15' )
Where "ElecTotalID" = '9'
But alas, when I try to place this update statement in a macro, correct syntax as best as possible, and assign to a button, I receive syntax or no valid table errors. I think I am close, but just not quite there. Thank you for the help you have given and for any future endowment.
when concatenating lines it’s wise to add a space before the end of line " (double quote).
e.g. 4999999AND should read 4999999 AND
.
remember because of the method you are using to connect, the database called “New” must be registered.
replace your existing sSQL with this:
EDIT: sorry just noticed you have added the field "FinishDate"
sSQL = "UPDATE ""Electricity Monthly Totals"" SET ""Cost"" = " & _
"(" & _
"SELECT " & _
"SUM(""NoUnits"" * ""Power"" * ""OpDays"" * 24 / 1000 * 0.09983) " & _
"+ " & _
"(" & _
"SELECT SUM(""Cost"") " & _
"FROM ""Expenses Table"" " & _
"WHERE ""ExpCatID"" = 8 " & _
"AND ""AcctID"" BETWEEN 4999000 AND 4999999 " & _
"AND ""Date"" BETWEEN '2024-05-01' AND '2024-05-31' " & _
"AND ""ConJobID"" in(13,14) " & _
") " & _
"FROM ""Elec Est Table"" " & _
"WHERE ""FinishDate"" BETWEEN '2024-03-16' AND '2024-04-15' " & _
") " & _
"WHERE ""ElecTotalID"" = 9"Thank you, that cleaned up the syntax for me, and thanks for the tips.
In my Options, it says that my “New” database is listed as registered. Is there another way I should confirm? I am still getting the No Valid Table error
Please show the sql-code by msgbox sSQL.
@TNBuilder,
don’t think I have ever seen this error message before.
.
I copied and used the wrong “FinishDate” values in my previous post.
this sql contains the same values that you successfully executed, could you please execute this from menu:>Tools>SQL and post the outcome.
UPDATE "Electricity Monthly Totals" SET "Cost" =
(
SELECT SUM("NoUnits" * "Power" * "OpDays" * 24 / 1000 * 0.09983)
+
(
SELECT SUM("Cost")
FROM "Expenses Table"
WHERE "ExpCatID" = 8
AND "AcctID" BETWEEN 4999000 AND 4999999
AND "Date" BETWEEN '2024-05-01' AND '2024-05-31'
AND "ConJobID" in(13,14)
)
FROM "Elec Est Table"
WHERE "FinishDate" BETWEEN '2024-04-16' AND '2024-05-15'
)
WHERE "ElecTotalID" = 9Hi RobertG
I had forgot to remove an apostrophe or i would have sent it with above. Thx!
@TNBuilder,
ok so lets try a slightly different approach.
1} hit Queries icon.
2) hit Create Query in SQL View.
3) menu:>Edit>Run SQL command directly.
4) paste the sql from my previous post.
5) save as “qDO_UPDATE”.
.
paste this code into a module, execute it directly from the IDE and post the outcome:
Sub DoUpdate
Dim oContext As Object
Dim oDatabase As Object
Dim oConnection As Object
Dim oStatement As Object
Dim sSQL As String
dim oQueries as object, oQuery as object
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDatabase = oContext.getByName("New")
oConnection = oDatabase.getConnection("", "")
oQueries = oConnection.queries
oQuery = oQueries.getbyname("qDO_UPDATE")
sSQL = oQuery.command
oStatement = oConnection.createStatement()
oStatement.execute(sSQL)
'msgbox sSQL
End SubResults:
The SQL (when ran by clicking on the “Run Query”, says “Statement does not generate a Result Set”)
This happens with any SQL statement other than SELECT statements.
Finally:
UPDATE "Electricity Monthly Totals" SET "Cost" = (SELECT "Cost" FROM "your_view").
update_button.odb (64.2 KB)
P.S. Documentation of the attachment:
Storing calculated data in cached tables is bad practice. In rare cases it may help to avoid running expensive queries over and over again. However, you should be aware that the stored values may not up to date.
Wow, please dont take this wrong, I never believed that it would take so much to make my query into a button. But, Thank You!, This gives me something to work on.
BTW, I ran the above instructions, made my view, and it worked. I ran the UPDATE through TOOLS – SQL and it said it updated 16 rows, the amount of rows I have in the table, but only the correct month changed.
I am going to study the .odb you sent and see what I can figure out. Thanks again for all the help, and hopefully, when I get it accomplished, again hopefully within a few days, I’ll update the post.
My next project is how to open a report or form using a listbox, so wish me luck. Thanks!
you have to appreciate that I have been unable to test anything.
it would appear that star basic validates that the query is in fact a real query when executing the command getbyname(“QueryName”).
I took a look at some of my existing databases and can see that I used different coding:
oStatement.execute(oQueries.QUERY_NAME.command)
I wonder if you would take the time to try this, the code is only slightly different.
rename the query “qDO_UPDATE” as “DO_UPDATE” then execute this code from the IDE:
Sub DoUpdate2
Dim oContext As Object
Dim oDatabase As Object
Dim oConnection As Object
Dim oStatement As Object
Dim sSQL As String
dim oQueries as object, oQuery as object
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDatabase = oContext.getByName("New")
oConnection = oDatabase.getConnection("", "")
oQueries = oConnection.queries
oStatement = oConnection.createStatement()
oStatement.execute(oQueries.DO_UPDATE.command)
'msgbox sSQL
End SubTada! Success. I was able to use this explanation to update tables from a button. At first, I forgot to limit it to only one entry, so all entries were updated, but a small edit to add a WHERE to the UPDATE statement in the Additional Information of the button solved the problem of having to do it manually. Thank you! And, thanks to all that helped. I received some great tips for a beginner.
