Have a query driven by a multi combo box form. User can search the database and spit out a report -- which gets populated in a subform -- by filling in multiple criteria and hitting search.
One combo box queries the status of the project -- draft 1, draft 2, final, completed, cancelled, on hold, etc.
Need to include in the dropdown for project status "active", meaning all projects that aren't completed, cancelled or on hold.
Actually, I'd like active to be the default value and we only check for completed or cancelled when the user selects that, otherwise it's assumed we're looking at active projects only.
Unfortunately, there was no active value when the table was originally built.
So when active is selected in the dropdown the query needs to select not the active projects, but rather all projects that are NOT Completed are NOT Cancelled and are NOT on hold.
Would think it would be some kind of if then statement, though I'm confused on how to go about it and where it would go. On the form, in the query, or would it be a macro?
The sql for the query was:
SELECT DISTINCTROW t_Projects.TPR, t_Projects.[TPR Date], t_Projects.Requester, t_Projects.[Publication #], t_Projects.[Alternate #], t_Projects.[publication type], t_Projects.Description, t_Projects.Model, t_Projects.[Due Date], t_Projects.Writer, t_Projects.Comments, t_Projects.[Fast Track], t_Projects.[Revision Level], t_Projects.Complexity, t_Projects.OnTime, t_Projects.Platform, t_Projects.[Revision Level], t_ReviewStatus.[Review Stage]
FROM (t_Projects INNER JOIN t_ReviewStatus ON t_Projects.TPR = t_ReviewStatus.TPR) LEFT JOIN t_ReviewResponses ON t_ReviewStatus.ReviewStatusId = t_ReviewResponses.ReviewStatusId
WHERE (t_Projects.TPR=[Forms]![F_Project_Tracker2]![TPR_Search] OR [Forms]![F_Project_Tracker2]![TPR_Search] IS NULL)
AND (t_Projects.Requester=[Forms]![F_Project_Tracker2]![Request_Search] OR [Forms]![F_Project_Tracker2]![Request_Search] IS NULL)
...
AND (t_ReviewStatus.[Review Stage]=[Forms]![F_Project_Tracker2]![Status_Search] OR [Forms]![F_Project_Tracker2]![Status_Search] IS NULL);
In query couldn't get anything that would work correctly.
Any help, suggestions would be great.