I have a table that contains a field called StateCode:
My state codes reflect USA, Mexico, Canada etc.. so state codes could be larger than 2 positions.
I need to update another table using information from this table.
In a Where statement, I need to be able to compare everything to the left of the - to something and I also need to compare everything to the right of the - to something else.
I tried doing this but get 'InStr' is not a recognized built-in function name.
WHERE Substring(StateCode,1,(InStr(StateCode,"-")-1)) = StateProvinceCode and Substring(StateCode,(InStr(StateCode,"-")+1),50) = StateProvinceCountryCode
Is there another way to accomplish this ?