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

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

returning zero: putting
(rather than Div By

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

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.

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.

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