InStr function

[lovas]

Hello, I am building reports for the internet. When this particular report is run, a sub on read is performed and inside of that I want an InStr function to look in a table.column for various data and if it finds any of them to display Mechanical or Motors in the appropriate place on the page. I am sorry, I cannot figure out how to write it, this will help.

String being searched = deliveries.line_code
String to find = C30, C31, D00, D10, D11, D37
Variable = ProdLineCode

If it finds any of the 'String to find' values it must display Mechanical in the variable position.

I tried this:

If InStr(ssr_deliveries_line_code,C30, C31, D00, D10, D11, D37,) then
ProdLineCode = "Mechanical"
elseif InStr(ssr_deliveries_line_code,A40, B00, B01, B02, B03, B04, B10, B11, B12,) then
ProdLineCode = "Motors"
end if

Please Help?

[karlmcauley]

As far as i am aware you will not be able to do this with the instr function alone. Code that would probably do it in VB script is:

Function Test_STR(ssr_deliveries_line_code, strTest,charWidth)

Dim strTest, ssr_deliveries_line_code, strStatus, instrPos
Dim booStatus

strTest = "C30, C31, D00, D10, D11, D37"
charWidth = "3"
strStatus=False
instrPos=1

Do until instrPos> len(strTest)
if inSTR(ssr_deliveries_line_code,instrPos,charWidth)>0 Then
strStatus=True
end if
intstrPos = intstrPos + charwidth
loop

Test_STR = strStatus

End Function

Sub Mechanical_YN

Dim booStatus
booStatus = Test_STR(ssr_deliveries_line_code, strTest,charWidth)

strTest = "A40, B00, B01, B02, B03, B04, B10, B11, B12"

If booStatus = True then
productlinecode = "mechanical"
else
booStatus = Test_STR(ssr_deliveries_line_code, strTest,charWidth)

if booStatus = true then
productlinecode = "Motors"
else
productlinecode = "Cant find it!!"
end if

end if

End Sub



I know it seems complicated but it should work.....Alternatively to save all that i would just create another table in your database that saves the code and the description and link this back to your main query....this way you only have to check a database field to see what product line it is.

hope this helps.

[lovas]

Thank You very much I will try it.