When one criteria is NULL

[GohDiamond]

When one criteria needs to be NULL

Hi guys,

I'm stuck on a Query criteria. I'm trying to use a single query to display two recordsets. One is a subset of the other.

I have a Managing Director and a a few Hiring Managers . They are associated with a number of job positions (60 Total). All of the jobs are assigned to the Director, but some of the jobs are assigned to the Managers.

I need to see the list of jobs NOT assigned to any Hiring Managers. (20 Jobs out of the 60 directly reporting to the Director)

The criteria is read from 2 comboboxes on the form. The list of associated jobs appears in the subform(query).

When only the Managing Director's combo box is selected I'd like the list to be limited to that director's name excluding any jobs assigned to a Hiring Manager (Essentially these are the jobs that the Hiring Managers occupy)

When Both the Managing Director and the Hiring Manager have been selected I'd like only the list for that Hirimg Manger Displayed.

I've tried:

I've tried Master/Child relationships but it displays nothing if one of the comboboxes is Null.

I've also tried this:


WHERE POSITIONS.POS_Managing_DIR =[forms]![frm_POSITIONS]![cbo_Emp_Managing_DIR]) AND ((POSITIONS.POS_Hiring_MGR)=[forms]![frm_POSITIONS]![cbo_Emp_Hiring_MGR] Or (POSITIONS.POS_Hiring_MGR) Is Null))
ORDER BY POSITIONS.POS_Position_ID, POSITIONS.POS_Hiring_MGR, POSITIONS.POS_MGR, POSITIONS.POS_Group_MGR;

But it displays both the Managing Director's jobs and the Hiring Manager's jobs
when both comboboxes have been selected and only the managing Director's jobs when no hiring manager has been selected. (close)

Can anybody fix me?

Thanks in advance for any help

Cheers!
Goh

 

[namliam]

Your solution is in NOT trying to do two things in one go...

Adjust your sql in code to make it do what you want...

i.e.

mySQL = ""
mySQL = mySQL & " Select .... "
mySQL = mySQL & " From ... "
If isnull([forms]![frm_POSITIONS]![cbo_Emp_Hiring_MGR]) then
'Assuming the combobox is a string value
    mySQL = mySQL & " Where POSITIONS.POS_Managing_DIR = """ & [forms]![frm_POSITIONS]![cbo_Emp_Managing_DIR] & """ "
'Assuming the combobox is a date value
    mySQL = mySQL & " Where POSITIONS.POS_Managing_DIR = #" & [forms]![frm_POSITIONS]![cbo_Emp_Managing_DIR] & "# "
'Assuming the combobox is a number value
    mySQL = mySQL & " Where POSITIONS.POS_Managing_DIR = " & [forms]![frm_POSITIONS]![cbo_Emp_Managing_DIR] & " "
else
    mySQL = mySQL & " Where POSITIONS.POS_Hiring_MGR = & [forms]![frm_POSITIONS]![cbo_Emp_Hiring_MGR] 
endif 
mySQL = mySQL & " Order by ... " 

Currendb.querydefs("Yourqueryname").sql = mysql

' From here open the report or query to display your required results

Good luck and good hunting