Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQL extra filtering query

22 views
Skip to first unread message

colmkav

unread,
May 23, 2012, 12:24:46 PM5/23/12
to
Hi,

I want to filter further the results I get on a query which contains 2
fields "average no of transactions" and "no of transactions". I want
to return only records where the no of transactions is less than 20%
of the average.

I use the following new field:

Expr1: 1.2*[SumOfCreated transactions]-[AvgOfCreated transactions]
> 0

But I get a message "Overflow"

Why is this? And Is there a better way of doing this in the query?

Patrick Finucane

unread,
May 23, 2012, 1:12:22 PM5/23/12
to
Here's a small routine for you to run and test
Sub JJJ()
Dim iI As Integer
iI = 32000
Do While iI < 1000000
iI = iI + iI
Loop
MsgBox "Done"
End Sub

iI quickly exceeds the maximum value of an integer and throws out an
"Overflow" message

You also have Expr1: 1.2*[SumOfCreated transactions]-[AvgOfCreated
transactions]. Using the order of precedence your statement becomes
(1.2 * [SumOfCreated transactions]) Minus [AvgOfCreated
transactions]. Is that really what you want?

colmkav

unread,
May 23, 2012, 1:45:47 PM5/23/12
to
On May 23, 6:12 pm, Patrick Finucane <patrickfinucan...@gmail.com>
wrote:
Sorry, there are indeed brackets around the expression so order of
precedence is fine.

So what should do I do instead of? Can it be done in one SQL
statement? Or is better to use recordsets and write some VBA code?

.

Patrick Finucane

unread,
May 23, 2012, 3:05:40 PM5/23/12
to
> .- Hide quoted text -
>
> - Show quoted text -

Sometimes I'll create Query1 to collect the major data. Then create a
second query that uses Query1 to drill down further. Ex: You could
have something like
Select Query1,* From Query1 Where ((1.2 * Clng([SumOfCreated
transactions])) - Clng([AvgOfCreated)) > 0

Bob Barrows

unread,
May 24, 2012, 6:52:57 AM5/24/12
to
An overflow occurs when you try to put a vlue into a space in memory that is
not large enought to hold that value.
I would start by using a test query to create a column each component of
that calculation so you can determine which component is causing the
overflow. Then use decide what to do. If it's the Sum operation, You might
try using CDbl to cast the no or transactions to Double before summing them.
It is unlikely that you can exceed the max value of a Double type, but if
you do, you can try dividing them by 1000, for example, before summing them,
doing the same to the average so you have apples and apples.


colmkav

unread,
May 24, 2012, 7:50:28 AM5/24/12
to
> doing the same to the average so you have apples and apples.- Hide quoted text -
>
> - Show quoted text -

thanks. I have been using clng. I think my main problem though was
dividing by zero so I have put IIF statement around it and now I dont
get an error.

0 new messages