Math is wrong...I know it's my fault, but I don't know how to fix it 1

[LongFeiFengWu]

Here's the code for some math I need done on my page:

FOR i = 5 to objrec.fields.count - 1

IF objrec.fields(i).value > 0 THEN thecount = thecount + 1

fieldvalue=objrec.fields(i).value

AHTC = objrec("JANAHTC") + objrec("FEBAHTC") + objrec("MARAHTC") + objrec("APRAHTC") + objrec("MAYAHTC") + objrec("JUNAHTC") + objrec("JULAHTC") + objrec("AUGAHTC") + objrec("SEPAHTC") + objrec("OCTAHTC") + objrec("NOVAHTC") + objrec("DECAHTC")
AHTE = objrec("JANAHTE") + objrec("FEBAHTE") + objrec("MARAHTE") + objrec("APRAHTE") + objrec("MAYAHTE") + objrec("JUNAHTE") + objrec("JULAHTE") + objrec("AUGAHTE") + objrec("SEPAHTE") + objrec("OCTAHTE") + objrec("NOVAHTE") + objrec("DECAHTE")
AHTO = objrec("JANAHTO") + objrec("FEBAHTO") + objrec("MARAHTO") + objrec("APRAHTO") + objrec("MAYAHTO") + objrec("JUNAHTO") + objrec("JULAHTO") + objrec("AUGAHTO") + objrec("SEPAHTO") + objrec("OCTAHTO") + objrec("NOVAHTO") + objrec("DECAHTO")
CPH = objrec("JANCPH") + objrec("FEBCPH") + objrec("MARCPH") + objrec("APRCPH") + objrec("MAYCPH") + objrec("JUNCPH") + objrec("JULCPH") + objrec("AUGCPH") + objrec("SEPCPH") + objrec("OCTCPH") + objrec("NOVCPH") + objrec("DECCPH")
ATT = objrec("JANATT") + objrec("FEBATT") + objrec("MARATT") + objrec("APRATT") + objrec("MAYATT") + objrec("JUNATT") + objrec("JULATT") + objrec("AUGATT") + objrec("SEPATT") + objrec("OCTATT") + objrec("NOVATT") + objrec("DECATT")
TIPS = objrec("JANTIPS") + objrec("FEBTIPS") + objrec("MARTIPS") + objrec("APRTIPS") + objrec("MAYTIPS") + objrec("JUNTIPS") + objrec("JULTIPS") + objrec("AUGTIPS") + objrec("SEPTIPS") + objrec("OCTTIPS") + objrec("NOVTIPS") + objrec("DECTIPS")
OBS = objrec("JANOBS") + objrec("FEBOBS") + objrec("MAROBS") + objrec("APROBS") + objrec("MAYOBS") + objrec("JUNOBS") + objrec("JULOBS") + objrec("AUGOBS") + objrec("SEPOBS") + objrec("OCTOBS") + objrec("NOVOBS") + objrec("DECOBS")
REV = objrec("JANREV") + objrec("FEBREV") + objrec("MARREV") + objrec("APRREV") + objrec("MAYREV") + objrec("JUNREV") + objrec("JULREV") + objrec("AUGREV") + objrec("SEPREV") + objrec("OCTREV") + objrec("NOVREV") + objrec("DECREV")
MEET = objrec("JANMEET") + objrec("FEBMEET") + objrec("MARMEET") + objrec("APRMEET") + objrec("MAYMEET") + objrec("JUNMEET") + objrec("JULMEET") + objrec("AUGMEET") + objrec("SEPMEET") + objrec("OCTMEET") + objrec("NOVMEET") + objrec("DECMEET")
DEV = objrec("JANDEV") + objrec("FEBDEV") + objrec("MARDEV") + objrec("APRDEV") + objrec("MAYDEV") + objrec("JUNDEV") + objrec("JULDEV") + objrec("AUGDEV") + objrec("SEPDEV") + objrec("OCTDEV") + objrec("NOVDEV") + objrec("DECDEV")

YTDAHTC = formatDIV(AHTC,thecount,1)
YTDAHTE = formatDIV(AHTE,thecount,1)
YTDAHTO = formatDIV(AHTO,thecount,1)
YTDCPH = formatDIV(CPH,thecount,1)
YTDATT = formatDIV(ATT,thecount,1)
YTDTIPS = formatDIV(TIPS,thecount,1)
YTDOBS = formatDIV(OBS,thecount,1)
YTDREV = formatDIV(REV,thecount,1)
YTDMEET = formatDIV(MEET,thecount,1)
YTDDEV = formatDIV(DEV,thecount,1)

next

%>

What's happening is that the columns, as you may notice are JAN thru DEC with whatever the field data is. JANAHTC thru DECAHTC as an example. I did this for each data type in a single table. What I need to have happen is to have AHTC = JANAHTC thru DECAHTC and then YTDAHTC = formatDIV(AHTC,the count of JANAHTC thru DECAHTC where the value > 0 ,1). What I'm getting with my current script is the count of all columns in the table with values > 0 and that's messing up the math. Anyone have any ideas? Should I scrap this and start over?

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[DreXor]

writing up some code for you, will post soon.

DreX
aKa - Robert

 

[DreXor]

Pre-Apologies for the upcoming lengthy post...

DreX
aKa - Robert

 

[DreXor]

wow that's not fun to try and read..
should make some kind of loop for the months or something it's be alot easier on the eyes... and will probably help you in the resolution you're looking for :

please forgive any typos, i'm not at a machine i can look over this code very well.

i dont understand the purpose of the field loop you have in your code, so i'm going to strip it so as to only focus on the counts/zeros, and sums.. also seems all these fields are part of the same recordset, and as a result, i will shrink as much of this down to a little code as possible, and for sake of mass code and conditionals, i'm going to throw this into a dynamic loop where variables will be dimmed, named, created, and populated automatically, it might get a little confusing so PLEASE feel free to ask questions about what's going on

<%
Dim Months, Vars '[green] Dimming out you main array sets, for handling all these variables[/green]
Months = Split("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",",")
Vars = Split("AHTC,AHTE,AHTO,CPH,ATT,TIPS,OBS,REV,MEET,DEV",",") '[green] all your totals values which will be used to create variables later

' now just a note before the fireworks begin, there's a strong distinct pattern in all this math, you have VVV = MMMVVV 3 letter month and 3-4 letter variable, which directly matches the variable name it's being assigned to.
' so this will lead to the manufacturing of variables, and fieldnames, which will look like Vars(X) = RsObject(Months(Y) & Vars(X)) which will correspond to the arrays created above, and then there will be your individual counts, that will look like Var(X) & "_Count" which will house the values of each variable type-count where values aren't = 0
' a LOT of what's going to be going on will be fairly invisible, what i will try to do is after the code write out what's "happening" in all of this or what it would look like normally without the execute statements and the arrays
' when i reference or use the word var or vars it's just short lingo for variable(s)
' The [red]MARK INDICATORS[/red] are for reference in the full code below and they're locations vs the loops that follow and what the loop does.

' and so the plot thickens....

'Dimming out your vars and setting them to zero
'along with your var specific counter(non zero count)
' AND  your YTD variable names[/green]

'[red] MARK INDICATOR 1[/red]
for V=0 to Ubound(Vars)
'[green]actual variable[/green]
  Execute("Dim " & Vars(V))
  Execute(Vars(V) & "=0")
'[green]Variable counter[/green]
  Execute("Dim " & Vars(V) & "_Count")
  Execute(Vars(V) & "_Count=0")
'[green]YTD Variables[/green]
  Execute("Dim YTD" & Vars(V))
  Execute("YTD" & Vars(V) & "=0")
next

'[red] MARK INDICATOR 2[/red]
For V=0 to Ubound(Vars) '[green] for each of the var[iable] headings[/green]
  for M=0 to ubound(Months) '[green]runs 0-11 because of the array instead of 1-12[/green]
    Execute(Vars(V) & "=" & Vars(V) & " + objrec(""" & Months(M) & Vars(V) & """)")
    Execute("If objrec(""" & Months(M) & Vars(V) & """) > 0 Then " & Vars(V) & "_Count=" & Vars(V) & "_Count+1")
  Next
Next

'[red] MARK INDICATOR 3[/red]
For V=0 to Ubound(Vars)
  Execute("YTD" & Vars(V) & " = formatDIV(" & Vars(V) & "," & Vars(V) & "_Count,1)")
Next

'[green] now all you do is reference your YTD variables YTDAHTE, YTDAHTO, etc as you used to...[/green]
%>

[COLOR=black cyan]PLEASE NOTE ALL THE VARIABLES YOU HAD BEFORE ARE STILL THERE, so you can still reference them later in your page if needed[/color]


NOW ... what this would look like without the arrays and execs, just showing what everything is doing line for line ( and you can copy this code if it makes more sense to you )

[red] MARK INDICATOR 1[/red]
Dim AHTC
AHTC=0
Dim AHTC_Count
AHTC_Count=0
Dim YTDAHTC
YTDAHTC=0
Dim AHTE
AHTE=0
Dim AHTE_Count
AHTE_Count=0
Dim YTDAHTE
YTDAHTE=0
Dim AHTO
AHTO=0
Dim AHTO_Count
AHTO_Count=0
Dim YTDAHTO
YTDAHTO=0
Dim CPH
CPH=0
Dim CPH_Count
CPH_Count=0
Dim YTDCPH
YTDCPH=0
Dim ATT
ATT=0
Dim ATT_Count
ATT_Count=0
Dim YTDATT
YTDATT=0
Dim TIPS
TIPS=0
Dim TIPS_Count
TIPS_Count=0
Dim YTDTIPS
YTDTIPS=0
Dim OBS
OBS=0
Dim OBS_Count
OBS_Count=0
Dim YTDOBS
YTDOBS=0
Dim REV
REV=0
Dim REV_Count
REV_Count=0
Dim YTDREV
YTDREV=0
Dim MEET
MEET=0
Dim MEET_Count
MEET_Count=0
Dim YTDMEET
YTDMEET=0
Dim DEV
DEV=0
Dim DEV_Count
DEV_Count=0
Dim YTDDEV
YTDDEV=0


[red] MARK INDICATOR 2[/red]
AHTC=AHTC + objrec("JANAHTC")
If objrec("JANAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("FEBAHTC")
If objrec("FEBAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("MARAHTC")
If objrec("MARAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("APRAHTC")
If objrec("APRAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("MAYAHTC")
If objrec("MAYAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("JUNAHTC")
If objrec("JUNAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("JULAHTC")
If objrec("JULAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("AUGAHTC")
If objrec("AUGAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("SEPAHTC")
If objrec("SEPAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("OCTAHTC")
If objrec("OCTAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("NOVAHTC")
If objrec("NOVAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTC=AHTC + objrec("DECAHTC")
If objrec("DECAHTC") > 0 Then AHTC_Count=AHTC_Count+1

AHTE=AHTE + objrec("JANAHTE")
If objrec("JANAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("FEBAHTE")
If objrec("FEBAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("MARAHTE")
If objrec("MARAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("APRAHTE")
If objrec("APRAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("MAYAHTE")
If objrec("MAYAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("JUNAHTE")
If objrec("JUNAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("JULAHTE")
If objrec("JULAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("AUGAHTE")
If objrec("AUGAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("SEPAHTE")
If objrec("SEPAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("OCTAHTE")
If objrec("OCTAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("NOVAHTE")
If objrec("NOVAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTE=AHTE + objrec("DECAHTE")
If objrec("DECAHTE") > 0 Then AHTE_Count=AHTE_Count+1

AHTO=AHTO + objrec("JANAHTO")
If objrec("JANAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("FEBAHTO")
If objrec("FEBAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("MARAHTO")
If objrec("MARAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("APRAHTO")
If objrec("APRAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("MAYAHTO")
If objrec("MAYAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("JUNAHTO")
If objrec("JUNAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("JULAHTO")
If objrec("JULAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("AUGAHTO")
If objrec("AUGAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("SEPAHTO")
If objrec("SEPAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("OCTAHTO")
If objrec("OCTAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("NOVAHTO")
If objrec("NOVAHTO") > 0 Then AHTO_Count=AHTO_Count+1

AHTO=AHTO + objrec("DECAHTO")
If objrec("DECAHTO") > 0 Then AHTO_Count=AHTO_Count+1

CPH=CPH + objrec("JANCPH")
If objrec("JANCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("FEBCPH")
If objrec("FEBCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("MARCPH")
If objrec("MARCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("APRCPH")
If objrec("APRCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("MAYCPH")
If objrec("MAYCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("JUNCPH")
If objrec("JUNCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("JULCPH")
If objrec("JULCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("AUGCPH")
If objrec("AUGCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("SEPCPH")
If objrec("SEPCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("OCTCPH")
If objrec("OCTCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("NOVCPH")
If objrec("NOVCPH") > 0 Then CPH_Count=CPH_Count+1

CPH=CPH + objrec("DECCPH")
If objrec("DECCPH") > 0 Then CPH_Count=CPH_Count+1

ATT=ATT + objrec("JANATT")
If objrec("JANATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("FEBATT")
If objrec("FEBATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("MARATT")
If objrec("MARATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("APRATT")
If objrec("APRATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("MAYATT")
If objrec("MAYATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("JUNATT")
If objrec("JUNATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("JULATT")
If objrec("JULATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("AUGATT")
If objrec("AUGATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("SEPATT")
If objrec("SEPATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("OCTATT")
If objrec("OCTATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("NOVATT")
If objrec("NOVATT") > 0 Then ATT_Count=ATT_Count+1

ATT=ATT + objrec("DECATT")
If objrec("DECATT") > 0 Then ATT_Count=ATT_Count+1

TIPS=TIPS + objrec("JANTIPS")
If objrec("JANTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("FEBTIPS")
If objrec("FEBTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("MARTIPS")
If objrec("MARTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("APRTIPS")
If objrec("APRTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("MAYTIPS")
If objrec("MAYTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("JUNTIPS")
If objrec("JUNTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("JULTIPS")
If objrec("JULTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("AUGTIPS")
If objrec("AUGTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("SEPTIPS")
If objrec("SEPTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("OCTTIPS")
If objrec("OCTTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("NOVTIPS")
If objrec("NOVTIPS") > 0 Then TIPS_Count=TIPS_Count+1

TIPS=TIPS + objrec("DECTIPS")
If objrec("DECTIPS") > 0 Then TIPS_Count=TIPS_Count+1

OBS=OBS + objrec("JANOBS")
If objrec("JANOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("FEBOBS")
If objrec("FEBOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("MAROBS")
If objrec("MAROBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("APROBS")
If objrec("APROBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("MAYOBS")
If objrec("MAYOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("JUNOBS")
If objrec("JUNOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("JULOBS")
If objrec("JULOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("AUGOBS")
If objrec("AUGOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("SEPOBS")
If objrec("SEPOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("OCTOBS")
If objrec("OCTOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("NOVOBS")
If objrec("NOVOBS") > 0 Then OBS_Count=OBS_Count+1

OBS=OBS + objrec("DECOBS")
If objrec("DECOBS") > 0 Then OBS_Count=OBS_Count+1

REV=REV + objrec("JANREV")
If objrec("JANREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("FEBREV")
If objrec("FEBREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("MARREV")
If objrec("MARREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("APRREV")
If objrec("APRREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("MAYREV")
If objrec("MAYREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("JUNREV")
If objrec("JUNREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("JULREV")
If objrec("JULREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("AUGREV")
If objrec("AUGREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("SEPREV")
If objrec("SEPREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("OCTREV")
If objrec("OCTREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("NOVREV")
If objrec("NOVREV") > 0 Then REV_Count=REV_Count+1

REV=REV + objrec("DECREV")
If objrec("DECREV") > 0 Then REV_Count=REV_Count+1

MEET=MEET + objrec("JANMEET")
If objrec("JANMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("FEBMEET")
If objrec("FEBMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("MARMEET")
If objrec("MARMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("APRMEET")
If objrec("APRMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("MAYMEET")
If objrec("MAYMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("JUNMEET")
If objrec("JUNMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("JULMEET")
If objrec("JULMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("AUGMEET")
If objrec("AUGMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("SEPMEET")
If objrec("SEPMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("OCTMEET")
If objrec("OCTMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("NOVMEET")
If objrec("NOVMEET") > 0 Then MEET_Count=MEET_Count+1

MEET=MEET + objrec("DECMEET")
If objrec("DECMEET") > 0 Then MEET_Count=MEET_Count+1

DEV=DEV + objrec("JANDEV")
If objrec("JANDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("FEBDEV")
If objrec("FEBDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("MARDEV")
If objrec("MARDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("APRDEV")
If objrec("APRDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("MAYDEV")
If objrec("MAYDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("JUNDEV")
If objrec("JUNDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("JULDEV")
If objrec("JULDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("AUGDEV")
If objrec("AUGDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("SEPDEV")
If objrec("SEPDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("OCTDEV")
If objrec("OCTDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("NOVDEV")
If objrec("NOVDEV") > 0 Then DEV_Count=DEV_Count+1

DEV=DEV + objrec("DECDEV")
If objrec("DECDEV") > 0 Then DEV_Count=DEV_Count+1

[red] MARK INDICATOR 3[/red]
YTDAHTC = formatDIV(AHTC,AHTC_Count,1)
YTDAHTE = formatDIV(AHTE,AHTE_Count,1)
YTDAHTO = formatDIV(AHTO,AHTO_Count,1)
YTDCPH = formatDIV(CPH,CPH_Count,1)
YTDATT = formatDIV(ATT,ATT_Count,1)
YTDTIPS = formatDIV(TIPS,TIPS_Count,1)
YTDOBS = formatDIV(OBS,OBS_Count,1)
YTDREV = formatDIV(REV,REV_Count,1)
YTDMEET = formatDIV(MEET,MEET_Count,1)
YTDDEV = formatDIV(DEV,DEV_Count,1)

enjoy

DreX
aKa - Robert

 

[LongFeiFengWu]

WoW! That's a lot! Thank you very much for doing that for me. It makes all the sense in the world seeing it written out like that.

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[LongFeiFengWu]

I keep getting the error "Type Mismatch: 'ubound'" on line 40. Line 40 is FOR v=0 TO ubound(vars(v)). There are 4 columns in this table that are not numbers that are part of any math. 2 text 1 autonumber and 1 number (Year). These 4 all come before the math data in the table. Does that make a difference with this script?

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[DreXor]

whopops typo, take out the (V) in that line... parens included, i had different "handling" on the for/next's the first one was for each var in vars, and the other two were handled as for V=0 etc...
did a search/replace on that portion and appearantly missed that replaced instance..


so it will look like this :
FOR v=0 TO ubound(vars)

also as for your question about the extraneous fields... absolutely no problem, all the fields are directly referenced in the code, you can have as many extra fields as you want, as long as all the fields used in the loops are accounted for.




DreX
aKa - Robert

 

[LongFeiFengWu]

You are a life saver! Thank you so much!

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[DreXor]

PS ... Subnote:
where the for/next cycles are
you can do a little ginsu action on the code and very quickly convert this to something that can run on a full recordset, meaning, run for each record in a collection as part of a do while not RsObject.eof kind of thing, just incase this is part of a huge loop/report

to chop it up , all you need to do is replicate the first for loop, move the dims only to a new loop outside the recordset loop, and same with the array assignments, everything else would go inside your main recordset loop

so it'd look something like this ( minus all the commenting ) :

<%
Dim Months, Vars 
Months = Split("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",",")
Vars = Split("AHTC,AHTE,AHTO,CPH,ATT,TIPS,OBS,REV,MEET,DEV",",")
'[green] Just The Dims; assignments come later because they will need resetting in the recordset loop for every new instance (record)[/green]
for V=0 to Ubound(Vars)
  Execute("Dim " & Vars(V))
  Execute("Dim " & Vars(V) & "_Count")
  Execute("Dim YTD" & Vars(V))
next



'[green] WHATEVER CODE blah blah[/green]


Do while not objrec.eof
'[green] MARK INDICATOR 1[/green]
for V=0 to Ubound(Vars)
  Execute(Vars(V) & "=0")
  Execute(Vars(V) & "_Count=0")
  Execute("YTD" & Vars(V) & "=0")
next

'[green] MARK INDICATOR 2[/green]
For V=0 to Ubound(Vars)
  for M=0 to ubound(Months) 'runs 0-11 because of the array instead of 1-12
    Execute(Vars(V) & "=" & Vars(V) & " + objrec(""" & Months(M) & Vars(V) & """)")
    Execute("If objrec(""" & Months(M) & Vars(V) & """) > 0 Then " & Vars(V) & "_Count=" & Vars(V) & "_Count+1")
  Next
Next

'[green] MARK INDICATOR 3[/green]
For V=0 to Ubound(Vars)
  Execute("YTD" & Vars(V) & " = formatDIV(" & Vars(V) & "," & Vars(V) & "_Count,1)")
Next

'[green] DO all your variable handling/response.writes, whatever here[/green]

objrec.movenext
loop
%>

DreX
aKa - Robert

 

[DreXor]

oh forgot to mention, nice part about the variable arrays... is they can be used to make some pretty nifty table output with only a couple lines of code as well.. falls under the "whatever here" part of the comments up there..


<table> b4 the do while statement and </table> after the loop statement
for each record, make a <tr> (probably want this right under the do while statement) and </tr>( just before the movenext)

in the handle here portion
say you want the YTD values written out in a table

for V=0 to ubound(Vars)
  response.write "<td>"
  Execute("Response.Write YTD" & vars(V))
  Response.Write "</td>" & vbcrlf '[green] ( this is just for view source friendly output )[/green]
next

DreX
aKa - Robert

 

[LongFeiFengWu]

Ok, having a slight problem with this. It needs to be able to display out to 2 decimal places, but it's rounding the numbers to the nearest whole number. Where in this script can I fix that?

&quot;If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo.&quot; ~ Bruce Lee

 

[DreXor]

well if all the db fields being handled are numbers which if i remember right they are you can change this line out :

Execute(Vars(V) & "=" & Vars(V) & " + objrec(""" & Months(M) & Vars(V) & """)")


for :

Execute(Vars(V) & "=" & Vars(V) & " + FormatNumber(objrec(""" & Months(M) & Vars(V) & """),2)") ' ofor 2 decimal place handling


DreX
aKa - Robert

 

[LongFeiFengWu]

thank you very much!

&quot;If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo.&quot; ~ Bruce Lee

 

[DreXor]

looked again, you could also do the formatnumber in mark set 3 for the totals, or before markset 3 you could replicate markset 1 and do the following:

for V=0 to Ubound(Vars)
  Execute(Vars(V) & "=FormatNumber(" & vars(V) & ",2)")
next

this will cause a post sum conversion to 2 digit decimals

DreX
aKa - Robert

 

[LongFeiFengWu]

This is what it looks like now. I changed it a bit based on what you told me before.

Dim months, vars

months = Split("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",",")
vars = Split("AHTC,AHTE,AHTO,CPH,ATT,TIPS,OBS,REV,MEET,DEV",",")

FOR v=0 TO ubound(vars)
	Execute("Dim " & vars(v))
	Execute("Dim " & vars(v) & "_count")
	Execute("Dim YTD" & vars(v))
next

do while not objrec.eof

FOR v=0 TO ubound(vars)
	Execute(vars(v) & "=0")
	Execute(vars(v) & "_count=0")
	Execute("YTD" & vars(v) & "=0")
next

For v=0 to ubound(vars)
	For m=0 to ubound(months)
		Execute(vars(v) & "=" & vars(v) & " + FormatNum(objrec(""" & months(m) & vars(v) & """),2)")
		Execute("IF objrec(""" & months(m) & vars(v) & """) > 0 THEN " & vars(v) & "_count=" & vars(v) & "_count+1")
	next
next

FOR v=0 TO ubound(vars)
	Execute("YTD" & vars(v) & " = formatDIV(" & vars(v) & "," & vars(v) & "_count,1)")
next

&quot;If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo.&quot; ~ Bruce Lee

 

[DreXor]

and someday i hope you can establish yourself enough to get the inner workings of that mess understood, it's a quaint vbs trick. For what it seems you're doing, you could use as many tricks as you can get. saves you inordinate amounts of typing for starters

DreX
aKa - Robert

 

[LongFeiFengWu]

Ok, I'm lost. The changes I made didn't work.

Execute(vars(v) & "=" & vars(v) & " + FormatNum(objrec(""" & months(m) & vars(v) & """),2)")

Didn't make it 2 decimals. I wonder if there's something else that needs changing that I'm missing.

&quot;If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo.&quot; ~ Bruce Lee

 

[DreXor]

well it depends what values need 2 decimals?
the ytd values? or all the others?


DreX
aKa - Robert

 

[LongFeiFengWu]

I think my Access 2000 DB is changing the numbers that get entered. I think it's rounding them up or down to the nearest whole number and I can't fix it for some reason. The data type is Number and the properties are set to Long Integer with 2 decimal places, so I don't know what's going on.

&quot;If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo.&quot; ~ Bruce Lee

 

[LongFeiFengWu]

The YTD values are fabulous the way they are. All the other values need to be 2 decimal places.

&quot;If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo.&quot; ~ Bruce Lee