Help with DCOUNT overflow problem 
Author Message
 Help with DCOUNT overflow problem

Hi,

The Percentage is UPDATED for Day 1 of the table and then
an overflow happens. The count works but the divison to
get percentages does not work. Overflow must indicate

Set rst = db.OpenRecordset("Shipped Via")

With rst
   While Not .EOF

strSQL = "UPDATE [Shipped Via] SET [Shipped Via].[Central
Economy] = format(DCOUNT(""[Oracle PO ID
Reference2]"", ""[DDay" & rst![Day] & "]"",""[CDC Region
Name]='Central' AND [Carrier Cd]= 'AA2'"")/DCOUNT
(""[Oracle PO ID Reference2]"", ""[DDay" & rst![Day]
& "]"",""[CDC Region Name]='Central'""),'Percent') WHERE
[Shipped Via].[Day] = " & rst![Day] & ";"

db.Execute strSQLCalc, dbFailOnError

   Wend
End With

Thanks,

Hank



Tue, 24 May 2005 00:55:38 GMT  
 Help with DCOUNT overflow problem


Quote:
> Hi,

> The Percentage is UPDATED for Day 1 of the table and then
> an overflow happens. The count works but the divison to
> get percentages does not work. Overflow must indicate

> UPDATE [Shipped Via]
> SET [Central Economy] =
>   FORMAT(DCOUNT(""[Oracle PO ID Reference2]"",
>          ""[DDay" & rst![Day] & "]"",
>          ""[CDC Region Name]='Central' AND [Carrier Cd]= 'AA2'"")
>          /
>          DCOUNT(""[Oracle PO ID Reference2]"",
>                 ""[DDay" & rst![Day] & "]"",
>                 ""[CDC Region Name]='Central'""),
>          'Percent')
> WHERE [Shipped Via].[Day] = " & rst![Day] & ";"

you've not protected yourself against the DCounts returning zero: putting
this in the immediate window produces an overflow error (rather than Div By
Zero):

    ? format(0/0,"percent")

Second, it's got to be easier to do this in a single SQL query hasn't it?

Tim F



Tue, 24 May 2005 03:45:58 GMT  
 Help with DCOUNT overflow problem

Quote:
>-----Original Message-----


>> Hi,

>> The Percentage is UPDATED for Day 1 of the table and
then
>> an overflow happens. The count works but the divison
to
>> get percentages does not work. Overflow must indicate

>> UPDATE [Shipped Via]
>> SET [Central Economy] =
>>   FORMAT(DCOUNT(""[Oracle PO ID Reference2]"",
>>          ""[DDay" & rst![Day] & "]"",
>>          ""[CDC Region Name]='Central' AND [Carrier Cd]
= 'AA2'"")
>>          /
>>          DCOUNT(""[Oracle PO ID Reference2]"",
>>                 ""[DDay" & rst![Day] & "]"",
>>                 ""[CDC Region Name]='Central'""),
>>          'Percent')
>> WHERE [Shipped Via].[Day] = " & rst![Day] & ";"

>you've not protected yourself against the DCounts

returning zero: putting
Quote:
>this in the immediate window produces an overflow error

(rather than Div By

Quote:
>Zero):

>    ? format(0/0,"percent")

>Second, it's got to be easier to do this in a single SQL
query hasn't it?

>Tim F

>.

Tim,

Do you insert ? format(0/0,'percent') somewhere in the
code to fix the problem? Is this a fix or a question? I'm
using this procedure to get daily data for a month for
graphs in Excel. A single SQL query? I don't see any
other way, given the amount of data being calculated at
79,000 records. This type of query structure runs stats
very fast and is suitable for what needs to be done. The
denominator DCount source may be the problem here causing
the overflow error. I may have to create a new field of
counts being performed in the denominator and replace the
dcount in the denominator with [Shipped Via].[Count of
Central], the new field. Have tried several arrangements
with no luck.

Thanks,

Hank



Tue, 24 May 2005 09:18:11 GMT  
 Help with DCOUNT overflow problem


Quote:
> Do you insert ? format(0/0,'percent') somewhere in the
> code to fix the problem? Is this a fix or a question?

It's a way of demonstrating the error: type that command into the Immediate
Window and you'll get the overflow error. I think you are getting the
overflow error because you are dividing by zero.

Quote:
> I'm
> using this procedure to get daily data for a month for
> graphs in Excel. A single SQL query? I don't see any
> other way, given the amount of data being calculated at
> 79,000 records.

Given that amount of data I'd be breaking rocks trying to do it a single
query... but the logic is too obscure for me to see how it might be done.
"If it's not broken, then don't fix it" is a good rule for life, but it
also appears that it is broken.

Quote:
> This type of query structure runs stats
> very fast and is suitable for what needs to be done. The
> denominator DCount source may be the problem here causing
> the overflow error.

Yes, that's what I said above. Prolly the best thing is to get the two
DCounts separately into two variables and then check before doing the
division.  

BoL

Tim F



Wed, 25 May 2005 03:27:58 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. DCOUNT divided by DCOUNT

2. Need help with weird overflow problem.

3. Help declaring variable? (overflow problem)

4. Help with OVERFLOW problem

5. Help!Overflow problem

6. Dcount VBA syntax problem

7. DCount problem

8. DCount - Problems

9. DCount and Date Problem

10. Problems with DCount function

11. Dcount function - need help with the criteria part!

12. Help Beginner with DCOUNT function

 

 
Powered by phpBB® Forum Software