Math in queries vs defined data types in VBA

[tcb]

I ran a query to square numbers and found that if I do this in a
query:

99999*99999=9999800320

If I define the variables as double in code the result is correct:

99999*99999=9999800001

What is the explanation for this?

 

[Allen Browne]

I can't reproduce the problem you described.

Using Access 2007, I created this query:
SELECT 99999*99999 AS Result;
Running the query displays 9999800001.

If you do need to force the numbers to doubles in a query for some reason,
use CDbl(), e.g.:
SELECT CDbl(99999) * CDbl(99999) AS Result;

 

[tcb]

Interesting anomaly. I am using Access 2003. FYI here is a sample of
query results compared to what it should be:

SELECT tbl_WN.WN, [WN]*[WN] AS WNxWN, tbl_WN.Square
FROM tbl_WN

I populated the square field in VBA defining variables as double.

WN WNxWN Square
100000 10000000000 10000000000
99999 9999800320 9999800001
99998 9999599616 9999600004
99997 9999399936 9999400009
99996 9999200256 9999200016
99995 9998999552 9999000025
99994 9998799872 9998800036
99993 9998600192 9998600049
99992 9998400512 9998400064

Thanks always for your great responses. Can I ship you a pound of
coffee? Wine? Beer?

 

[Allen Browne]

No: I still can't repro that in A2003 either.
I created tbl_WN, with 1 field of type Number, size Double.
I ran your query (without the Square field), and got the right answers.

The answers were correct with a Number of size Decimal as well.
Naturally they overflowed with a size Long Integer.

I did get exactly your problem answers with a Number of type Single, which
would also be technically correct as Single doesn't have enough significant
figures.

 

[Allen Browne]

Output from Immediate Window, after changing the strings to:
Debug.Print dblHold, dblHold ^ 2

100000 10000000000
99999 9999800001
99998 9999600004
99997 9999400009
99996 9999200016
99995 9999000025
99994 9998800036
99993 9998600049
99992 9998400064

At a quick visual scan, that's the same as the MsgBox.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen -

What does your system return calling the following?
Public Sub SquareEm()
'From debug window - Call SquareEm

Dim dblHold As Double
Dim n As Integer
Dim NL As String
Dim TB As String
Dim strHold As String

NL = Chr(13) & Chr(10) ' Define newline.
TB = Chr(9)

strHold = "Root " & TB & "Squared" & NL & NL
dblHold = 100000
For n = 1 To 9
strHold = strHold & dblHold & TB & dblHold ^ 2 & NL & NL
dblHold = dblHold - 1
Next n
MsgBox strHold, vbOKOnly, "Example"
End Sub

Bob

I can't reproduce the problem you described.

[quoted text clipped - 5 lines]

use CDbl(), e.g.:
SELECT CDbl(99999) * CDbl(99999) AS Result;

[OT]: I can reproduce a different spurious result using the OP's SQL:

Sub bigerrnum()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
cat.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
cat.ActiveConnection.ConnectionString
.CursorLocation = 3
.Open
Dim rs
Set rs = .Execute( _
"SELECT 99999*99999")
MsgBox rs(0)
End With
End Sub

The returned value is (usually) 3.13380280183498E-294 (Double).

Jamie.

--