[cperrywebrat]

I have solved the problem - I printed the SQL3 string and OtherDepartment2 thru OtherDepartment5 had an extra space in front of the field, so I did a Trim(OtherDepartmentX) and retrieved the values
************************************
Original problem below
************************************
I am baffled. I have been spending several hours to figure out why I am not pulling all records from my database. I have an sql statement:

SQL3="SELECT * FROM department_table WHERE (department='" & department & _
"' OR department='" & homeDepartment & _
"' OR department='" & OtherDepartment1 & _
"' OR department='" & OtherDepartment2 & _
"' OR department='" & OtherDepartment3 & _
"' OR department='" & OtherDepartment4 & _
"' OR department='" & OtherDepartment5 & "')"

I have values for all of the above which match. However, only the department, homeDepartment, and OtherDepartment1 gets pulled from the db.
Here is my complete code:

SQL3="SELECT * FROM department_table WHERE (department='" & department & _
"' OR department='" & homeDepartment & _
"' OR department='" & OtherDepartment1 & _
"' OR department='" & OtherDepartment2 & _
"' OR department='" & OtherDepartment3 & _
"' OR department='" & OtherDepartment4 & _
"' OR department='" & OtherDepartment5 & "')"

SET cn=Server.CreateObject("ADODB.Connection")
openStr="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../../db/SafetyReports/SafetyReports.mdb")
cn.OPEN openStr
SET records=Server.CreateObject("ADODB.Recordset")

records.OPEN SQL3, cn, 2, 2
IF records.BOF or records.EOF THEN
directors="Not found"
ELSE
DO WHILE NOT records.EOF
Response.Write records("ID") & " - "
Response.Write records("department") & " - "
Response.Write records("dirUsername") & " - "
Response.Write records("admUsername") & "<BR>"
records.MoveNext
LOOP
END IF
records.CLOSE