Originally Posted by CJ_London
because you are referencing a form variable, the ODBC driver will return everything to then compare against the form variable
you also have # in a field name - do not use non alpha numeric characters (except underscore) it can cause issues
with regards the iif - bad bit of coding. I'm not going to try to understand why you are doing it that way - would suggest wear etc should be in a lookup table and PCL_Category should be numeric linked to the lookup PK
with regards the vba, the build would be something like
dim sqlStr as string
sqlStr="SELECT......FROM.....WHERE PCL_ToolNumber=" & me.txtTM_ToolNumber & " AND PCL_Category IN (" & ……
a lot of this code is pieced from older & other databases, and some from back when i knew a lot less about what i was doing....
I've significantly sped this form up just by eliminating a few IIF's, and Dlookups from some of my queries (i also deleted an entire tab that was running a pretty intense query) now i'm down to less than 1.5 sec to open which i can live with for now.