Filtering for

[Calhoontuna]

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.

[rpeare]

include a calculated field in your query:

ActiveFlag: iif([status] <> 'cancelled' and [status] <> 'closed' and [status] <> 'hold', 'Active', 'Inactive')

then add an additional element to your search routine to find 'active' or 'inactive' on the activeflag field.

[Calhoontuna]

Hm.

Every time I add this to the main query the thing blows up and give me a query "too complex" error. Not sure if this is because there's so many drop down it overwhelms the query or not.

Though I do think that this was the approach I was hoping for. Had never heard of the active switch before. One question that could probably be easily fixed is that the database has thousands of rows that when you go through all of them it starts taking time crunching. Wasn't sure if this active switch would lop time off the lag after the guy hits search or not.

[orange]

Perhaps you could show us your table(s) design.
Are you familiar with Normalization? It is a fundamental concept of relational database.

rpeare was showing you how to isolate the Active projects from All projects based on the value of ProjectStatus.

See the Planning and Design link in my signature for a variety of articles that may help with your project.

[Micron]

So when active is selected in the dropdown the query needs to select not the active projects, but

I find much about the entire explanation confusing. There is no active value in some table, yet there is active in a combo box. Or is there? If you select Active, return everything that is not active.
Sounds like the process will need some kind of work around due to improper table design. Getting "Active" in a combo list shouldn't be too hard (could use a UNION query) but I think there's more to it. Maybe need code to build the WHERE part of a sql statement based on combo selection. A query really isn't the place to handle multiple form field variations IMHO. BTW, the error you mention can sometimes be due to improper parenthesis grouping, a mis-placed comma etc. which causes unintentional creation of some complex expression where you didn't intend it. To test that and any similar situation, one should build in small increments, testing each as you go.

[CJ_London]

give me a query "too complex" error

this usually means you have nulls in your data, try changing to criteria to wrap the nz function around the field values e.g.

nz(t_Projects.Requester)=Forms]![F_Project_Tracker2]![Request_Search]…..

[Calhoontuna]

[QUOTE=Ajax;429751]this usually means you have nulls in your data, try changing to criteria to wrap the nz function around the field values e.g.

nz(t_Projects.Requester)=Forms]![F_Project_Tracker2]![Request_Search]…..[/QUOTE


AJax, you're right. Was using Nulls.

Tried using nz but something must be alluding me here because it's not working. This pulls up nothing in my search:
AND NZ (t_Projects.[Alternate #]=[Forms]![F_Project_Tracker2]![Alternate_Search],"")

What would the following look like wrapped in nz like you were describing:
(t_Projects.Requester=[Forms]![F_Project_Tracker2]![Request_Search] OR [Forms]![F_Project_Tracker2]![Request_Search] IS NULL)

Digging further I'm seeing that when access "blows up" as I put it a previous email, what is happening is that it locks up for a long time and the reason it's locking up is because it's producing in sql incredibly complex code that fills up the screen for pages. Further, I'm seeing that it does this on earlier versions on the project that don't have the ActiveFlag.

[CJ_London]

compare your code with my example - your ) is in the wrong place

[Calhoontuna]

Ajax,

Thanks. Not sure but I think after digging into it deeper seemed I needed space between the , and ""). That seemed to cure it.

Regardless after I used your NZ suggestion that did in fact cure me of my access blowing up problem.

The new thing I'm on is selecting all projects that are NOT Completed, NOT Cancelled, and NOT On hold with a switch.

Whenever I try to say I get: "The expression you entered has a function containing the wrong number of arguments"

What I had tried was the following with the idea that I'd add cancelled and on hold when I got this up and running.

Drop down on form -- [Form]![F_Project_Tracker2]![Status_Search] -- has the choices: Active, Completed, Cancelled, and On Hold.

Had added this as a field in the query:
Active: Switch([Form]![F_Project_Tracker2]![Status_Search]="Completed",[t_ReviewStatus].[Review Stage]="Completed",[t_ReviewStatus].[Review Stage]<>"Completed")

Is my logic right on this?

[CJ_London]

without knowing the context, no idea. But your switch statement does not look correct - google the switch function - you need an even number of parameters, and you have 3. - but also see post #2 where a solution was provided