Pi() function and update statement

[Jap@a]

Dear all,

I wanted to calculate the volume (of an egg) out of length and the width (measured) according to the following formula volume= (((1/6)* pi* Length* width^2)/1000)

But I don’t get the Sql-script working. First of all this PI() function is not working, second it seems like also the divisions are not really working.

I tried as well in Access as in Sqlite (Manager). But both are not working.

Hereunder what it should look like


UPDATE tableA
SET Volume=(((1/6)* pi* Length* width^2)/1000);

But also easier things like Volume= pi() or Volume = (1/2)Length are not working



So wondering if anybody here could help me out thanks a lot.

Brecht

 

[boblarson]

Did you add a function for pi? There is no built-in function for that within Access. You would have to build your own or use something someone else might have created.

 

[Jap@a]

So that would mean I would have to write such a function in VBA?

Or does anyone here has it already?

Thanks,
Brecht

 

[boblarson]

Copy this into a standard module (not form, report or class module but standard module) and name the module basMath.

Function pi() As Double
    pi = 3.14159265358979
End Function

 

[Thales750]

Copy this into a standard module (not form, report or class module but standard module) and name the module basMath.

Function pi() As Double
    pi = 3.14159265358979
End Function

Bob, shame on you, you left out some of the digits. I believe it goes a lot further to the right.

 

[boblarson]

Bob, shame on you, you left out some of the digits. I believe it goes a lot further to the right.

I DID put in more but Access truncated it probably due to the Double's precision. I would have used the Decimal datatype but it isn't available from VBA (at least that I've found).

 

[Thales750]

I DID put in more but Access truncated it probably due to the Double's precision. I would have used the Decimal datatype but it isn't available from VBA (at least that I've found).

Thast's right, Double has 14 digits to the right of the decimal.

 

[Thales750]

But I belive that will be more than accurate enough for Jap@a. 14 decimal places is smaller than an atom.

 

[gemma-the-husky]

surprising, you would think there would be a vbPi constant, would you not?

 

[boblarson]

surprising, you would think there would be a vbPi constant, would you not?

I would have thought there would have been something like that. But I couldn't find anything except in Excel.

 

[jdraw]

Bob,
Just curious why a Pi() Function and not just a constant?


This is an MS Access "Constants overview I found using Google.
http://msdn.microsoft.com/en-us/library/aa199607(v=office.10).aspx
Const conPI = 3.14159265

 

[boblarson]

Bob,
Just curious why a Pi() Function and not just a constant?


This is an MS Access "Constants overview I found using Google.
http://msdn.microsoft.com/en-us/library/aa199607(v=office.10).aspx
Const conPI = 3.14159265

Because it isn't built in and in order to use it in a query you still would need to wrap it in a function.

 

[jdraw]

Thanks. Makes sense.

 

[ChrisO]

Option Explicit
Option Compare Text


Public Declare Function timeGetTime Lib "Winmm.dll" () As Long


Public Sub Test()
    Dim lngI     As Long
    Dim lngStart As Long
    Dim dblPi    As Double
    
    lngStart = timeGetTime()
    For lngI = 1 To 1000000
        dblPi = Pi_1()
    Next lngI
    MsgBox timeGetTime() - lngStart     [color=green]'< 85  nanoseconds per call[/color]


    lngStart = timeGetTime()
    For lngI = 1 To 1000000
        dblPi = Pi_2()
    Next lngI
    MsgBox timeGetTime() - lngStart     [color=green]'< 156 nanoseconds per call[/color]

End Sub


Function Pi_1() As Double

    Pi_1 = 3.14159265358979
    
End Function


Public Function Pi_2() As Double
    [color=green]' This can be found on the web.
    ' It seems strange to me because it seems to be based
    ' on two constants and the Atn function based on a constant.[/color]
    
    Pi_2 = 4 * Atn(1)
    
End Function

 

[Jap@a]

Hi all,

Thanks all for the replies, and sorry I didn't reply anymore myself, I was a bit busy with other things.

So I think I managed to add this pi() function but still I don't get the update statement running.
Even if I try to run something like:

update Update_test
Set Volume=length*2;

It doesn't work I only get empty values for volume, if a run the same statement in SQLite manager it runs so the statement is fine right? But why doesn't it work in Access?

Thanks again,
B

 

[stopher]

For interest...

Pi = (2143/22)^0.25

(correct to 8 d.p.)