I have tried using the + sign in sql to add 3 feilds together, but it doesn’t give any results, and also tried the same in the query edit window on a new field after watching a youtube video on how to do what I want, but it didn’t work for me.
In sql I have
“Cost1” + “DelCost1” + “VAT1” AS “TotalCost1” FROM “tblRecInfo” this doesn’t give any results.
as well as this
“Cost1”, “DelCost1” ,“VAT1” (“Cost1” + “DelCost1” + “VAT1”) AS “TotalCost1” FROM “tblRecInfo” but that gives me a syntax error.
I have also looked in the HSQLDB User Guide but can’t find what I am looking for.
Thanks but it doesn’t give me any results
This is the whole SQL in the query I am using. Due to me not knowing about the data types being set correctly when I copied from Access database, everything was set to VARCHAR, so I copied some of the fields and added a 1 at the end so I could set the correct data type as currency etc.
SELECT “ID” AS “ID”, “BuyDate” AS “BuyDate”, “Item” AS “Item”, “Quantity” AS “Quantity”, “SupplierName” AS “SupplierName”, “TotalCost” AS “TotalCost”, “OrderID” AS “OrderID”, “SupplierPhone” AS “SupplierPhone”, “SupplierEmail” AS “SupplierEmail”, “Notes” AS “Notes”, “SupplierAddress” AS “SupplierAddress”, “EbaySale1”, “Quantity1”, “Cost1”, “DelCost1”, “TotalCost1” AS “Item”, “Cost1” * .2 AS “VAT1”, “Cost1” + “DelCost1” + “VAT1” AS “Cost1” + “DelCost1” + “VAT1” AS “TotalCost1” FROM “tblRecInfo”
One thing; I have VAT1 set to decimal 2 places and currency in tblRecInfo, but it doesn’t show as currency in the query, so I set it as currency again but it cannot be saved.
Cost1 Vat1 and DelCost1 are all set as decimal 2 places, and are in both the table and query. There is data in the table for Cost1 and DelCost1 but not VAT1, and the query has data for all 3 fields.
I have ran it from both SQL and the query, and gives the same result of no results.
I think I know now why no results, is because the VAT1 data is not in the table, and I have just entred a couple of VAT costs in the table and there are results now, DOH!
@Ratslinger , I am getting results now as I said before I did not put any data in the VAT1 field in the table.
It must be a thing with base that it reads from a table and not a query, does it?
This is what I used for the total cost1 :
SELECT “Cost1” + “DelCost1” + “VAT1” AS “TotalCost1” FROM “tblRecInfo”
and it works ok
This is what I used to calculate the VAT : “Cost1” * .2 AS “VAT1”
And that also works ok
I wasn’t getting any results because I did not know or realise that all the required data had to be entered into the table and not the query.
Ok if it, VAT1 was to be calculated in the query, which was my original requirement, and it was, why then did running the query not update the TotalCost1 fields.