Mutliplication with str function (1 Viewer)

[Ben_Entrew]

Hi all,

I got the following problem.

This code doesn't calculate the exact value:

Sub TEST()
Dim strSQL As String
Dim b As Double
DoCmd.SetWarnings False


b = DLookup("MC", "COMMON_DISTRIBUTION", "YEAR = 2015")

    strSQL = "Update TNS_HIST Set [TNS_in_TRY] = " & _
               " [TNS_in_TRY] + " & Str(b) & " * DSum(""TNS_in_TRY"",""TNS_HIST"",""Division = 'COMMON' AND Product_Class = 'Scrap Sales' And Reporting_Month = '042015'"") " & _
               " Where Division IN ('MC') AND Product_Class = 'Scrap Sales' and Reporting_Month = '042015'"

DoCmd.RunSQL strSQL

End Sub

My variable b is 0.230.
The TNS is multiplied with 0.228.
It seems a problem with the Str(b).

Can anyone help me out?

Thanks.

Regards,
Ben

 

[spikepl]

With floating point numbers there is no such thing as "exact".

1. How exactly do you know the value contained in b? Show how you get to see the value
2. How exactly do you know what TNS was multiplied by?Show how you get to see the value

What is the content of strSQL ? (debug.print strSQL)

I bet anything you like that there is no problem with the function Str.

 

[Ben_Entrew]

With Debug.Print b I get the right number 0.23.

I tried also to use " & b & ",but that doesn't work out...

 

[spikepl]

I posed 3 questions, got an answer to 1. If you want help you need to answer what is asked.

 

[Ben_Entrew]

Thanks spikepl,

I found the solution, it was based on the DSUM.
Now I sum up over the right values and get the expected result.

Thanks.

 

[JANR]

FYI the order of mathematical operations is different for a computer than a human.

For a human this:

2+2*4 = 16

A computer will look at the same expression and conclude that the answer is 10.

2*4+2 = 10

Be explicit in the order you do the math.

(2+2)*4=16

Regards

JanR