Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trouble expressing Nulls in my query

Status
Not open for further replies.

shanl

Technical User
Apr 2, 2007
41
US
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]
 

Code:
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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top