Type Mismatch Error 5

[charbrad]

I am getting a "Type Mismatch" error...and can use another set of eyes. What I am trying to accomplish is if LBS = 0 then the report should not print. I have hightlighted in bold where I think my problem is coming from.

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
[b]if NOT rs.EOF and rs("LBSProduced") > 0 then
p=rs.GetRows()
End if
rs.close[/b]

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE Outsourced = 'No' AND CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
pIH=rs.GetRows()
rs.close

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) 

LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT 

Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE Outsourced = 'Yes' AND CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
pOS=rs.GetRows()
rs.close

 

[charbrad]

CamaroLT, your suggestion worked perfectly...have a star.

 

[Chopstik]

Good catch, CamaroLT!

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Sheco]

You dont want the EOF conditional on the same line same line of code as any other conditional that involves a field value.

 

[CamaroLT]

Thank you. :]

Sheco is kinda correct as well. Depending on how the interpreter checks, it may do a FULL BOOLEAN EVALUATION.

What that means is, if you do the following:

if (not rs.eof) and (rs("SomeField")="Other")

Your code is going to blow up. The reason is that if EOF is false (Meaning its not at the end of the file) the first part is going to evaluate to TRUE, then check the Recordset.

The issue hits when you are at EOF.

If rs.eof is true, that means that the first part is going to be false. But because of full boolean evaluation, its going to check the condition on RS("SomeField"). But because there is no information there, the code is going to raise an exception.

If full boolean eval isn't occuring, then the code will work so long that you check for EOF as the first condition.


-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[Sheco]

VB does evaluate the entire line.

To test it do this: (with no "On Error Resume Next")
[tt]if False AND (1 / 0) then
'blah blah
end if[/tt]

It will cause a "divide by zero" error.

MicroSoft gives a lame excuse for why VB evaluates the second part. They say it is always required to evaluate the entire line with VB because it uses the same symbol for assigning values as it does for testing equivalence.

For example, in languages with "curly brace" syntax, you do this:[tt]
if (x == 1) // equivalence test
{ x = 0; // value assignment
}[/tt]

But in Visual Basic you do this:[tt]
if x = 1 then ' equivalence test
x = 0 ' value assignment
end if
[/tt]

So, the excuse is that because the "=" character is used for both, VB must always evaluate an entire line of code before determining the meaning of the syntax, even when the "=" does not appear in the code.

 

[CamaroLT]

Which would explain why other languages use different symbols for evaluation and for assignment.

Delphi/Pascal use = for testing equivalence, but := for assignment. C/C++ use = for assignment, == for testing.

Definitely one to keep in the back of the brain.

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[Tarwn]

Actually the symbols have nothing to do with it. Some languages are written to short circuit their conditional evaluations and some are not written to do that (it requires extra work to build in short-circuiting in many cases).

For an exmaple of this take a look at the newest additions to conditional operands in VB.Net (AndAlso, OrElse).

The fact that some languages return a boolean conditional on assignment does not control whether that language can short-circuit or not. ex: if(a==b && a=b+1)
In fact many languages will have the operands that allow short-circuiting and operands that will force full evaluation.

For example, Python:

# both comparisons will be evaluated in the following line
if a == 0 | a == 1:
   print "done"

# if the first comparison is true this will not evaluate the second one
if a == 0 or a == 1:
   print "done"

-T