Recordsource query criteria based on checkbox and combobox selections

[kjschmitz]

On my Form test_Projects I would like to be able to filter records based on both a project year combobox (cbo_Project_Year) selection and a completed projects checkbox (chk_Comp_Proj).
Here is the recordsource of the Form (qry_Overall_Project_Management):

SELECT Projects.Project_ID, Projects.Building_ID, Projects.Project_Name, Projects.Project_Number, Projects.Primary_PM_ID, Projects.Secondary_PM_ID, Projects.Arch_Contact_ID, Projects.Civil_Contact_ID, Projects.Mech_Contact_ID, Projects.Struct_Contact_ID, Projects.Surveyor_ID, Projects.Inspector_ID, Projects.Tech_Contact_ID, Projects.Contactor_ID, [Arch Consultants].Acrhitect_Firm_Name, Building.Building_Name, [Building Admins].Building_Admin, [Arch Contacts]![Arch_Contact_First_Name] & " " & [Arch Contacts]![Arch_Contact_Last_Name] AS Arch_Full_Name, [SPPS Project Managers]![PM_First_Name] & " " & [SPPS Project Managers]![PM_Last_Name] AS SPPS_Full_Name, [Arch Consultants].Architect_ID, Building.Building_Name, Projects.Project_Status_ID, Projects.Sub_Complete, Projects.Project_Year

FROM ([Arch Consultants] RIGHT JOIN [Arch Contacts] ON [Arch Consultants].Architect_ID = [Arch Contacts].Architect_ID) RIGHT JOIN ([SPPS Project Managers] RIGHT JOIN (([Building Admins] RIGHT JOIN Building ON [Building Admins].Building_Admin_ID = Building.Building_Admin_ID) RIGHT JOIN Projects ON Building.Building_ID = Projects.Building_ID) ON [SPPS Project Managers].PM_ID = Projects.Primary_PM_ID) ON [Arch Contacts].Arch_Contact_ID = Projects.Arch_Contact_ID

WHERE (((Projects.Project_Status_ID)=1 Or (Projects.Project_Status_ID)=2 Or (Projects.Project_Status_ID)=3 Or (Projects.Project_Status_ID)=5 Or (Projects.Project_Status_ID)=IIf([Forms]![test_Projects]![chk_Comp_Proj]=0,0,4)) AND ((Projects.Project_Year)=IIf([Forms]![test_Projects]![cbo_Project_Year]=2011,2011,2012)));

This form has a listbox (lst_Proj_Select) of records for the PMs to select from. Here is the listbox code:

'------------------------------------------------------------
' lst_Proj_Select_AfterUpdate
'
'------------------------------------------------------------
Private Sub lst_Proj_Select_AfterUpdate()
    [test_Project_Estimates]![lst_Proj_Comp].Requery
 
On Error GoTo lst_Proj_Select_AfterUpdate_Err

    DoCmd.SearchForRecord , "", acFirst, "[Project_Number] = " & "'" & Screen.ActiveControl & "'"
    'DoCmd.RunCommand acCmdRefresh

lst_Proj_Select_AfterUpdate_Exit:
    Exit Sub

lst_Proj_Select_AfterUpdate_Err:
     
    Resume lst_Proj_Select_AfterUpdate_Exit

End Sub

The criteria seems to pull the chk_Comp_Proj just fine, and projects accurately load throughout the form / subforms.
The criteria for the combobox updates the lst_Proj_Select, but I can not select a record from the listbox and have the form load it. It seems stuck on 2012 projects (although the listbox shows 2011 ones).

My inkling is that this is where the problem originates (right now projects are only 2011,2012 to try and get it working - but I'd prefer to leave it open to future expansion):

WHERE ((Projects.Project_Year)=IIf([Forms]![test_Projects]![cbo_Project_Year]=2011,2011,2012)))

cbo_Project_Year
----------------
Row Source:

SELECT DISTINCT Projects.Project_Year
FROM Projects
WHERE (((Projects.Project_Year) Is Not Null));

Row Source Type: Table/Query
Limit to List: Yes
Allow Edits: No
----------------

Most of this was done using the query editor within Access 2007, not directly in SQL. Any help would be greatly appreciated. Thank you.

 

[misscrf]

I think

WHERE ((Projects.Project_Year)=IIf([Forms]![test_Projects]![cbo_Project_Year]=2011,2011,2012)))

Should be

WHERE ((Projects.Project_Year)=IIf([Forms]![test_Projects]![cbo_Project_Year] in (2011,2011,2012)))

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[kjschmitz]

Thanks misscrf;

That allows my listbox to accurately display the projects by year, but I am still having an issue with the form loading the record (if not in the Default value year of cbo_Project_Year) when the listbox selection is made.

In other words:

1. I select 2012 from cbo_Project_Year, and the lst_Proj_Selection refreshes appropriately.
2. I select the record from lst_Proj_Selection
3. The record remains on the last record viewed (2011 record).

'------------------------------------------------------------
' lst_Proj_Select_AfterUpdate
'
'------------------------------------------------------------
Private Sub lst_Proj_Select_AfterUpdate()
    [test_Project_Estimates]![lst_Proj_Comp].Requery
 
On Error GoTo lst_Proj_Select_AfterUpdate_Err

    DoCmd.SearchForRecord , "", acFirst, "[Project_Number] = " & "'" & Screen.ActiveControl & "'"
    'DoCmd.RunCommand acCmdRefresh

lst_Proj_Select_AfterUpdate_Exit:
    Exit Sub

lst_Proj_Select_AfterUpdate_Err:
     
    Resume lst_Proj_Select_AfterUpdate_Exit

End Sub

This seems to be the area I need help with.

 

[kjschmitz]

Upon further struggles, I am convinced that this is my problem:

WHERE ((Projects.Project_Year)=IIf([Forms]![test_Projects]![cbo_Project_Year]=2011,2011,2012)))

Using the following still fails:

WHERE ((Projects.Project_Year)=IIf([Forms]![test_Projects]![cbo_Project_Year] in (2011),2011,2012))

The format of the field referenced from the table (Projects.Project_Year)is a Long Int.

I have tried adding the CLng expression within the IIf statement, but get an expression error. Any ideas on why this query criteria is failing?

WHERE ((Projects.Project_Year)=IIf(CLng([Forms]![test_Projects]![cbo_Project_Year])=2011,2011,2012)))

The query always goes to the default 2012 (2011 condition failed) state.