Trouble expressing Nulls in my query

[shanl]

I'm hoping this will be obvious as to what I'm doing wrong and why my VBA query is failing. The entire query works fine in my Access application as long as I take out the reference to Null. I've tried using
(EmployeeDept_qry2.dtActEnd) Is Null without luck and my last attempt was using the function
IsNull(EmployeeDept_qry2.dtActEnd) without luck.
I'm trying to include all rows that have nulls in them for the End date field EmployeeDept_qry2.dtActend

strSQL = "SELECT DISTINCT EmployeeDept_qry2.zLName, EmployeeDept_qry2.zFName, EmployeeDept_qry2.zBadgeID FROM EmployeeDept_qry2 WHERE EmployeeDept_qry2.zDeptDesc = " & "'" & [Forms]![Main_frm]![DepartmentList] & "'" & " AND (EmployeeDept_qry2.bFullTime) = " & True & " AND IsNull(EmployeeDept_qry2.dtActEnd)" & " ORDER BY [EmployeeDept_qry2.zLName]

 

[Andrzejek]

strSQL = "SELECT DISTINCT EmployeeDept_qry2.zLName, EmployeeDept_qry2.zFName, " _
& " EmployeeDept_qry2.zBadgeID FROM EmployeeDept_qry2 " _
& " WHERE EmployeeDept_qry2.zDeptDesc = '" & [Forms]![Main_frm]![DepartmentList] & "' " _
& " AND (EmployeeDept_qry2.bFullTime) = " & True & " AND [blue](EmployeeDept_qry2.dtActEnd IS NULL)[/blue] " _
& " ORDER BY [EmployeeDept_qry2.zLName]"

Have fun.

---- Andy

 

[shanl]

Andy,
Thanks for taking a stab at this. I coded my sql exactly as you had it but still the same error "No value given for one or more req parameters". When I remove the reference to the field that has the nulls it works just fine? Seems to be complaining about how that is coded. What I did find odd was when I would look in my Immediate window, it didn't seem like it was reflecting what I was supplying in my Access screen. I would test out a dept A and it would show Dept B that I may have used from a previous test. Not sure why? but the results on my Access screen was showing the correct dept (after I took the null criteria out) despite what my Immediate window showed.
Shan

 

[PHV]

Seems that EmployeeDept_qry2 don't have a field named dtActEnd ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shanl]

PH,
How impressive. You are amazing. While the query I copied to create qry2 from had the field in question when I had created it, you were absolutely right it was no longer in the list. For some reason it disappeared. Once I included, it worked like a charm. Thank you so much. I spent an entire day racking my brain on why this sql wasn't working. Thanks for the quick response. My hero.
Shan