CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
lunar8nrg
lunar8nrg

Reputation: 111

MS Access VBA Add Decimals to Output

I have data which have 2 decimal places (See Figure)enter image description here and am using the function to subtract the two values but I'm getting a rounded number as my output. I've tried to add float to the following expression "(L.M123_Corr_Integrator-R.M123_Corr_Integrator)" but it's throwing out an error. I'm not familiar with sql/vba code so any useful information would help.

sql = "UPDATE (SELECT R.DateLog , ( L.M123_Corr_Integrator - R.M123_Corr_Integrator) AS Gross " & _ "FROM tbl_M123_DataSet AS L INNER JOIN tbl_M123_DataSet AS R " & _ "ON L.DateLog = DATEADD('d', 1, R.DateLog)) as T " & _ "INNER JOIN tbl_M123_DataSet ON tbl_M123_DataSet.DateLog = T.DateLog " & _ "Set Gross_kWh = Gross" dbs.Execute sql, dbFailOnError MsgBox dbs.RecordsAffected & " rows affected on column GrossKW"

That query unobfuscated looks like this:

UPDATE
   (
      SELECT
         R.DateLog,
         L.M123_Corr_Integrator - R.M123_Corr_Integrator AS Gross
      FROM
         tbl_M123_DataSet AS L
         INNER JOIN tbl_M123_DataSet AS R
            ON L.DateLog = DATEADD('d', 1, R.DateLog)
   ) AS T
   INNER JOIN tbl_M123_DataSet
      ON tbl_M123_DataSet.DateLog = T.DateLog
SET Gross_kWh = Gross

Upvotes: 0

Views: 831

Answers (1)

JCro
JCro

Reputation: 696

It looks to me that you have the table field Gross_kWh set to integer. In the table design view, highlight the row Gross_kWh and set its number type to 'Double'.

Also be aware that Access will sometimes round numbers for display, but the decimal accurate number is stored in the table correctly.

Upvotes: 1

Related Questions