Can anyone help me figure out why the IF statement in the two WHILE loops I have in the following code will work correctly on the first time through the FOR loop but not on the second try. It finds a match and assigns the values on the first run through the FOR loop and everything is calculated and output correctly. On the second time through the FOR loop, even though the WHILE reaches a point where the compares match it does not assign the data to the variables. This is causing an overflow error because it is trying to divide by zero. The variables are cast because I get a type mismatch error if I do not have them cast, and I have tried several different casting combinations. I have bolded the two lines that are not working. Any help would be greatly appreciated.
<%For i = 0 to ubound(state)
rsQuotes.movefirst
'Reset variables
prevYearQuoteDollars = 0.0
currYearQuoteDollars = 0.0
prevYearQuoteCount = 0
currYearQuoteCount = 0
quoteDollarsDiff = 0.0
quoteCountDiff = 0
percentChangeDollars = 0.0
percentChangeCount = 0.0
prevDollarsPerQuote = 0.0
currDollarsPerQuote = 0.0
DollarsPerQuoteDiff = 0.0
percentChangeDollarsPerQuote = 0.0
compareYear = 0
compareState = 0%>
<tr>
<td><%= state(i) %></td>
<%rsQuotes.movefirst
'Get Previous Year Quote Dollars
While rsQuotes.eof = false
compareYear = rsQuotes("Year")
compareState = rsQuotes("State")
If (CLng(compareYear) = CLng(prevEndYear) AND CStr(compareState) = CStr(state(i))) Then
prevYearQuoteDollars = CCur(rsQuotes("netAmt"))
prevYearQuoteCount = CLng(rsQuotes("QuoteCount"))%>
<td><%= FormatCurrency(rsQuotes("netAmt")) %></td>
<%rsQuotes.movenext
Else
rsQuotes.movenext
End If
Wend
'Get Current Year Quote Dollars
rsQuotes.movefirst
While rsQuotes.eof = false
compareYear = rsQuotes("Year")
compareState = rsQuotes("State")
If (CLng(compareYear) = CLng(currEndYear) AND CStr(compareState) = CStr(state(i))) Then
currYearQuoteDollars = CCur(rsQuotes("netAmt"))
currYearQuoteCount = CLng(rsQuotes("QuoteCount"))%>
<td><%= FormatCurrency(rsQuotes("netAmt")) %></td>
<%rsQuotes.movenext
Else
rsQuotes.movenext
End If
Wend
'Calculate Quote Dollar Stats
quoteDollarsDiff = CCur(currYearQuoteDollars - prevYearQuoteDollars)
percentChangeDollars = CDbl(quoteDollarsDiff/prevYearQuoteDollars)%>
<!-- Output Count Data -->
<td><%= FormatCurrency(quoteDollarsDiff) %></td>
<td <% If percentChangeDollars < 0 Then %> style = "color:red;" <% End If %>><%= FormatPercent(percentChangeDollars) %></td>
<td><%= prevYearQuoteCount %></td>
<td><%= currYearQuoteCount %></td>
<%'Calculate Count Stats
quoteCountDiff = CLng(currYearQuoteCount - prevYearQuoteCount)
percentChangeCount = CDbl(quoteCountDiff/prevYearQuoteCount)%>
<!-- Output Count Stats -->
<td <% If quoteCountDiff < 0 Then %> style = "color:red;" <% End If %>><%= quoteCountDiff %></td>
<td <% If percentChangeCount < 0 Then %> style = "color:red;" <% End If %>><%= FormatPercent(percentChangeCount) %></td>
<%'Calculate Per Quote Stats
prevDollarsPerQuote = CCur(prevYearQuoteDollars/prevYearQuoteCount)
currDollarsPerQuote = CCur(currYearQuoteDollars/currYearQuoteCount)
DollarsPerQuoteDiff = CCur(currDollarsPerQuote - prevDollarsPerQuote)
percentChangeDollarsPerQuote = CDbl(DollarsPerQuoteDiff/prevDollarsPerQuote)%>
<!-- Output Per Quote Stats -->
<td><%= FormatCurrency(prevDollarsPerQuote) %></td>
<td><%= FormatCurrency(currDollarsPerQuote) %></td>
<td><%= FormatCurrency(DollarsPerQuoteDiff) %></td>
<td <% If percentChangeDollarsPerQuote < 0 Then %> style = "color:red;" <% End If %>><%= FormatPercent(percentChangeDollarsPerQuote) %></td>
</tr>
<%Next
<%For i = 0 to ubound(state)
rsQuotes.movefirst
'Reset variables
prevYearQuoteDollars = 0.0
currYearQuoteDollars = 0.0
prevYearQuoteCount = 0
currYearQuoteCount = 0
quoteDollarsDiff = 0.0
quoteCountDiff = 0
percentChangeDollars = 0.0
percentChangeCount = 0.0
prevDollarsPerQuote = 0.0
currDollarsPerQuote = 0.0
DollarsPerQuoteDiff = 0.0
percentChangeDollarsPerQuote = 0.0
compareYear = 0
compareState = 0%>
<tr>
<td><%= state(i) %></td>
<%rsQuotes.movefirst
'Get Previous Year Quote Dollars
While rsQuotes.eof = false
compareYear = rsQuotes("Year")
compareState = rsQuotes("State")
If (CLng(compareYear) = CLng(prevEndYear) AND CStr(compareState) = CStr(state(i))) Then
prevYearQuoteDollars = CCur(rsQuotes("netAmt"))
prevYearQuoteCount = CLng(rsQuotes("QuoteCount"))%>
<td><%= FormatCurrency(rsQuotes("netAmt")) %></td>
<%rsQuotes.movenext
Else
rsQuotes.movenext
End If
Wend
'Get Current Year Quote Dollars
rsQuotes.movefirst
While rsQuotes.eof = false
compareYear = rsQuotes("Year")
compareState = rsQuotes("State")
If (CLng(compareYear) = CLng(currEndYear) AND CStr(compareState) = CStr(state(i))) Then
currYearQuoteDollars = CCur(rsQuotes("netAmt"))
currYearQuoteCount = CLng(rsQuotes("QuoteCount"))%>
<td><%= FormatCurrency(rsQuotes("netAmt")) %></td>
<%rsQuotes.movenext
Else
rsQuotes.movenext
End If
Wend
'Calculate Quote Dollar Stats
quoteDollarsDiff = CCur(currYearQuoteDollars - prevYearQuoteDollars)
percentChangeDollars = CDbl(quoteDollarsDiff/prevYearQuoteDollars)%>
<!-- Output Count Data -->
<td><%= FormatCurrency(quoteDollarsDiff) %></td>
<td <% If percentChangeDollars < 0 Then %> style = "color:red;" <% End If %>><%= FormatPercent(percentChangeDollars) %></td>
<td><%= prevYearQuoteCount %></td>
<td><%= currYearQuoteCount %></td>
<%'Calculate Count Stats
quoteCountDiff = CLng(currYearQuoteCount - prevYearQuoteCount)
percentChangeCount = CDbl(quoteCountDiff/prevYearQuoteCount)%>
<!-- Output Count Stats -->
<td <% If quoteCountDiff < 0 Then %> style = "color:red;" <% End If %>><%= quoteCountDiff %></td>
<td <% If percentChangeCount < 0 Then %> style = "color:red;" <% End If %>><%= FormatPercent(percentChangeCount) %></td>
<%'Calculate Per Quote Stats
prevDollarsPerQuote = CCur(prevYearQuoteDollars/prevYearQuoteCount)
currDollarsPerQuote = CCur(currYearQuoteDollars/currYearQuoteCount)
DollarsPerQuoteDiff = CCur(currDollarsPerQuote - prevDollarsPerQuote)
percentChangeDollarsPerQuote = CDbl(DollarsPerQuoteDiff/prevDollarsPerQuote)%>
<!-- Output Per Quote Stats -->
<td><%= FormatCurrency(prevDollarsPerQuote) %></td>
<td><%= FormatCurrency(currDollarsPerQuote) %></td>
<td><%= FormatCurrency(DollarsPerQuoteDiff) %></td>
<td <% If percentChangeDollarsPerQuote < 0 Then %> style = "color:red;" <% End If %>><%= FormatPercent(percentChangeDollarsPerQuote) %></td>
</tr>
<%Next