Type mismatch

[Cheech]

I am trying to make an SQL statement work. I have built it up by adding strings together. it all worked fine until I added an extra bit of code to find the first occurrrence of the string "No" in an array i use the code below to do this:

arrConfirmed = Array(strC1, strC2, strC3, strC4, strC5, strC6)
Dim iLoop, strPos, strNO
iLoop = "0"
strPos = "-1"
strNo = "No"
Do While strPos < 0
If (arrConfirmed(iLoop) = strNo) Then
strPos = iLoop
ElseIf (iLoop = UBound(arrConfirmed)) Then
strPos = (iLoop + 1)
Else
iLoop = iLoop + 1
End If
Loop


This works no problem but then I tried to add the value of strPos to my SQL statement with this code

strSQL = (&quot;UPDATE tbl_sku_master SET stage = &quot; + strStage)
strSQL = strSQL + (&quot;, earlystage = &quot; + strPos)
strSQL = (strSQL + &quot;, conf_&quot; + strStage + &quot; = 'Yes'&quot
strSQL = (strSQL + &quot;, conf_by_&quot; + strStage + &quot; = &quot; + &quot;'&quot; + strOwner + &quot;'&quot
strSQL = (strSQL + &quot;, conf_date_&quot; + strStage + &quot; = &quot; + &quot;'&quot; + strNewDate + &quot;'&quot
strSQL = (strSQL + &quot; WHERE uniq_ref = &quot; + strREF + &quot;;&quot

this code works without the line that reads
&quot;strSQL = strSQL + (&quot;, earlystage = &quot; + strPos)&quot;

all I get if it is included is &quot;Type mismatch&quot;

AAAaahhhhhhhhggggggggggg(signs of significant hair loss)

I have checked the database column settings for &quot;earlystage&quot; and they are &quot;Number&quot; &quot;Long Integer&quot; I am using Access 2000 databases.

Anyone got an idea of what I am doing wrong???(PLEASE)
I dont want to go to Chelsea!!!

 

[link9]

strSQL = (&quot;UPDATE tbl_sku_master SET stage = &quot; + strStage)
strSQL = strSQL + (&quot;, earlystage = &quot; + strPos)
strSQL = (strSQL + &quot;, conf_&quot; + strStage + &quot; = 'Yes'&quot

should be:

strSQL = (&quot;UPDATE tbl_sku_master SET stage = &quot; + strStage)
strSQL = (strSQL + &quot;, earlystage = &quot; + strPos)
strSQL = (strSQL + &quot;, conf_&quot; + strStage + &quot; = 'Yes'&quot

had your paren in the wrong spot.


Paul Prewett

 

[phuctran]

And don't use + when you build your StrSQL. Use & instead.

Ex:
strSQL = &quot;Select * from &quot;
strSSQL = strSQL & &quot; mytable&quot;

 

[Cheech]

Cheers guys,,

I have eventually managed to solve all the problems with Type Mismatch etc.. by routing round about 3 different asp pages doing each step on one page then moving on to the next, and it was the &quot;&&quot;/&quot;+&quot; that was causing the problems. I dont want to go to Chelsea!!!