IF Statement only works on first run through For Loop

[bdc101]

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

 

[PHV]

Where are prevEndYear, currEndYear and state(X) defined/initialized ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdc101]

The variables are dimmed earlier in the program and are holding the dates. I used some response.write commands to check what they were holding, and they have the correct values. The response.writes actually showed that the variables compared in the IF statement matched and should have returned the value "true " but it won't execute the code inside the IF. I do not know if it skipping the IF statement somehow are getting a false return somehow.

 

[tsuji]

For the first while
[tt]
> <td><%= FormatCurrency(rsQuotes("netAmt")) %></td>
> <%rsQuotes.movenext
> Else
> rsQuotes.movenext
> End If
> Wend
[/tt]
[tt]
<td><%= FormatCurrency(rsQuotes("netAmt")) %></td>
' [COLOR=red yellow]<%[/color]rsQuotes.movenext
'Else
' rsQuotes.movenext
End If
rsQuotes.movenext
Wend
[/tt]
Other loops suffer something similar.

 

[tsuji]

Amendment for the movenext. It should be read like this.
[tt]
<% rsQuotes.movenext %>
[/tt]
- tsuji

 

[tsuji]

Sorry, it is this.
[tt]
<% rsQuotes.movenext
Wend %>
[/tt]
- tsuji

 

[bdc101]

Thanks for the help tsuji, but it did not work. NAy other suggestions?

-bdc101

 

[bdc101]

A little more information. I put some more response.write commands in the file, and it showed that the test is returning false on the 2nd time through the FOR loop. Any ideas why it would test false when it should test true. the data from the db looks good so I cannot see how it is determining the compare is false.

-bdc101

 

[tsuji]

First, the data pointer must move correctly. My posting sofar addresses that only. You have not a word on this. Does it make a difference in this aspect? so that thing moves correctly? Else, can look again into it.

As to compare conditional, that is a different aspect independent of the former. If anything which behaves unexpectedly, there won't be any magic force behind. You have to check [1] the data in the db at that location is correct, not null, not empty (as clng() them will be a runtime error which might be suppressed rather than blown up by on error statement.) [2] any control character like vbcr, vblf etc embedded in the strings so retrieved.

 

[bdc101]

The error is definitely data related, but i do not know how or why. I changed the code to put the data in arrays and added some statements to test for numeric values. The queries are pulling the correct data, but the VBScript will not read the numbers as a numeric value. Any ideas why? I cannot find any problems with the data that is returned by the queries. I am including the info that is returned by the response.writes I added to help see what the data is and follow the program somewhat.

Starting While Loop to put quote info query results into arrays.
Recordset(0) AR is a string. -added state
Recordset(0) 122496.81 is a number and was added to the array. -IsNumeric tested true for quoteDollars
Recordset(0) 11 is not a number and was not added to the array. -IsNumeric tested false for quoteCount
Recordset(0) 2004 is not a number and was not added to the array. -IsNumeric tested false for quoteYear
Recordset(1) AR is a string.
Recordset(1) 64407.8 is a number and was added to the array.
Recordset(1) 14 is not a number and was not added to the array.
Recordset(1) 2005 is not a number and was not added to the array.
Recordset(2) LA is a string.
Recordset(2) 58911.6 is a number and was added to the array.
Recordset(2) 13 is not a number and was not added to the array.
Recordset(2) 2004 is not a number and was not added to the array.
Recordset(3) LA is a string.
Recordset(3) 100757.05 is a number and was added to the array.
Recordset(3) 29 is not a number and was not added to the array.
Recordset(3) 2005 is not a number and was not added to the array.
Recordset(4) TX is a string.
Recordset(4) 198140.6 is a number and was added to the array.
Recordset(4) 51 is not a number and was not added to the array.
Recordset(4) 2004 is not a number and was not added to the array.
Recordset(5) TX is a string.
Recordset(5) 547502.95 is a number and was added to the array.
Recordset(5) 68 is not a number and was not added to the array.
Recordset(5) 2005 is not a number and was not added to the array.
Starting While Loop to put order info query results into arrays.
Recordset(0) AR is a string. -state added
Recordset(0) 3846.55 is a number and was added to the array. -IsNumeric tested true for orderDollars
Recordset(0) 5 is a number and was added to the array. -IsNumeric tested true for orderCount
Recordset(0) 2004 is not a number and was not added to the array. -IsNumeric tested false for orderYear
Recordset(1) AR is a string.
Recordset(1) 4106.95 is a number and was added to the array.
Recordset(1) 8 is a number and was added to the array.
Recordset(1) 2005 is not a number and was not added to the array.
Recordset(2) LA is a string.
Recordset(2) 7526.05 is a number and was added to the array.
Recordset(2) 9 is a number and was added to the array.
Recordset(2) 2004 is not a number and was not added to the array.
Recordset(3) LA is a string.
Recordset(3) 10101.675 is a number and was added to the array.
Recordset(3) 8 is a number and was added to the array.
Recordset(3) 2005 is not a number and was not added to the array.
Recordset(4) TX is a string.
Recordset(4) 13975.65 is a number and was added to the array.
Recordset(4) 23 is a number and was added to the array.
Recordset(4) 2004 is not a number and was not added to the array.
Recordset(5) TX is a string.
Recordset(5) 26443.2 is a number and was added to the array.
Recordset(5) 23 is a number and was added to the array.
Recordset(5) 2005 is not a number and was not added to the array.
Quote Info For Loop Run 0
Loop 1(0) Records: AR | 122496.81 | |
Loop 1(0) Compare variables: 0 | 2004 | AR | TX
Loop 1(0) Test : False
Microsoft VBScript runtime error '800a01a8'
Object required
/reports/outside-sales-reports/quarterly-report/generate-report-new.asp, line 273

 

[PHV]

I'd debug each field Type property too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdc101]

The data type for the results that are causing a problem are all field. How can I force a field data type into another type if using casts does not work?

P.S. Thanks for all the help everyone. It's greatly appreciated.

 

[PHV]

are all field
Sure, but what is the value of, say, rsQuotes("QuoteCount").Type

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdc101]

The value is the one shown in the response.writes referenced earlier. Here is the code for the loops that put the quote data in arrays with the resposne.writes (the loop for order data is the same way):

Dim quoteState(), quoteDollars(), quoteCount(), quoteYear(), i
i = 0
response.write("</br>Starting While Loop to put quote info query results into arrays.")
While Not rsQuotes.eof
Redim preserve quoteState(i)
Redim preserve quoteDollars(i)
Redim preserve quoteCount(i)
Redim preserve quoteYear(i)
quoteState(i) = CStr(rsQuotes("State"))
response.write("</br>Recordset("& (i) &") "& rsQuotes("State") &" is a string.")
If (IsNumeric(rsQuotes("netAmt"))) Then
quoteDollars(i) = CDbl(rsQuotes("netAmt"))
response.write("</br>Recordset("& (i) &") "& rsQuotes("netAmt") &" is a number and was added to the array.")
Else
response.write("</br>Recordset("& (i) &") "& rsQuotes("netAmt") &" is not a number and was not added to the array.")
response.write("</br>Field Data type is: "& TypeName(rsQuotes("netAmt")) &".")
End If
If (IsNumeric(rsQuotes("QuoteCount"))) Then
quoteCount(i) = CLng(rsQuotes("QuoteCount"))
response.write("</br>Recordset("& (i) &") "& rsQuotes("QuoteCount") &" is a number and was added to the array.")
Else
response.write("</br>Recordset("& (i) &") "& rsQuotes("QuoteCount") &" is not a number and was not added to the array.")
response.write("</br>Field Data type is: "& TypeName(rsQuotes("QuoteCount")) &".")
End If
If (IsNumeric(rsQuotes("Year"))) Then
quoteYear(i) = CLng(rsQuotes("Year"))
response.write("</br>Recordset("& (i) &") "& rsQuotes("Year") &" is a number and was added to the array.")
Else
response.write("</br>Recordset("& (i) &") "& rsQuotes("Year") &" is not a number and was not added to the array.")
response.write("</br>Field Data type is: "& TypeName(rsQuotes("Year")) &".")
End If
rsQuotes.movenext
i = i + 1
Wend

 

[bdc101]

Hello again. I have added some coed that is forcing every variable to be the data subtype I want now and I am still getting a false return on the compare. The data looks the same and the variables are the same subtype. I aplogize for being such a pest, but I just do not know what else to do. Is there any way to response.write the data to see nay hidden characters that may exist? I do not believe there aer any but I do not know what else it could be. Thanks again everyone for all the help.

 

[tsuji]

try
[tt] response.write escape(rsQuotes("State")) & "<br />" & escape(rsQuotes("Year")) & "<br />[/tt]

 

[bdc101]

One more thing to add. This program is still working properly the first time it runs through the parent FOR loop for the state. It always encounters the problem on the second run through the parent FOR loop. This is adding to my frustration because if it can find the records and test true on the first loop through why can't it on the second. According to the response.writes I have added nothing has changed except the values used, and there is a definite point where the data subtypes and values match for the IF statement. I am thoroughly confused by this. Thanks again for the help.

 

[tsuji]

Do you put "on error resume next" somewhere? Take it out. And it may very well rsQuotes.movefirst refuse to do it because of the cursortype or something.

 

[bdc101]

on error resume next" is not used anywhere. The program puts the recordsets in arrays now. I used the escape command and there are no special characters in the data. I am still having a problem with the second run through the loop. Should I cast my reset commands?

 

[bdc101]

Found the problem finally. I was forgetting to check the array "state" for special characters. There was a space in the data that was not supposed to be there. Thanks everyone for all the help.

--bdc101