Querying Different Values Based on Created Expression Within Query

[McArthurGDM]

Hi,

I have a query that is meant to search through a particular table of values.

I created another field in the query that evaluates whether or not the table of values satisfy the stage of a particular project (it's a lot of embedded IIF statements). This tells me whether or not the project is on STAGE 1, STAGE 2, STAGE 3, STAGE 4, COMPLETE, REQUIRES REVIEW. The actual expression is set up this way - if you are interested:

IIf([Stage_1_Status]="Complete" And [Stage_2_Status]="Confirmed" And Not IsNull([Stage_3_Status]) And [Stage_4_Status]="Complete","Complete",
IIf([Stage_1_Status]="Complete" And [Stage_2_Status]="Confirmed" And Not IsNull([Stage_3_Status]) And [Stage_4_Status]<>"Complete","Stage 4",
IIf([Stage_1_Status]="Complete" And [Stage_2_Status]="Confirmed" And Not IsNull([Stage_3_Status]) And IsNull([Stage_4_Status]),"Stage 4",


IIf([Stage_1_Status]="Complete" And [Stage_2_Status]="Confirmed" And IsNull([Stage_3_Status]),"Stage 3",
IIf([Stage_1_Status]="Complete" And [Stage_2_Status]<>"Confirmed","Stage 2",
IIf([Stage_1_Status]="Complete" And IsNull([Stage_2_Status]),"Stage 2",
IIf([Stage_1_Status]<>"Complete","Stage 1","Requires Review")))))))

Now, in my unbounded form I have it set up so that I can search on these particular values. I would like to be able to search as to whether or not the project is NOT COMPLETE. Right now my criteria is set up as such

Like "*" & [Forms]![MainNavigation]![NavigationSubform].[Form]![ProjectStatus] & "*" Or Is Null

I would like it so that I have a drop-down that has the values STAGE 1, STAGE 2, STAGE 3, STAGE 4, COMPLETE, NOT COMPLETE, REQUIRES REVIEW.

I tried to place an IIF statement in my criteria, but it is just not working.

Any suggestions?

[June7]

What does 'not working' mean - error message, wrong results, nothing?

What would determine the choice of "Not Complete" vs "Requires Review".

[McArthurGDM]

Sorry,

I just can't get it to work the way as intended. I get a myriad of errors depending on various approaches.

Requires review would mean that it didn't meet any of those criteria - hence something is wrong. "NOT COMPLETE" simply means <> "COMPLETE".

Based on what I currently have, what next steps would you take to be able to search on "NOT COMPLETE" within the same drop down on an unbounded form?

[ranman256]

Dont use the monster IF statments...instead use VBA to decifer your form into a single statement...
You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
sWhere= mid(sWhere,4)
  
'just use the form filter
me.filter = sWhere
me.filterOn = true

   'OR   apply the sql to the form
sSql = "SELECT * FROM tblCompany WHERE " & sWhere

'save the sql as a qry or open the sql

[June7]

If I understand, you need to calculate a value in query that filter criteria can be applied to. I still don't understand how the choice would be made between "Not Complete" vs "Requires Review". These appear synonymous to me.

What rule would determine "Not Complete" - all the stage fields are empty? any one is empty? any one is <> Complete?

[McArthurGDM]

REQUIRES REVIEW means that someone messed up something - probably. Say, for instance, that someone filled-in STAGE 3 but did not fill-in STAGE 2. The monster IIF statement says that STAGE 1, STAGE 2, STAGE 3, STAGE 4, and COMPLETE were not met and therefore something went wrong and hence REQUIRES REVIEW.

In this case, in the monster IIF statement, REQUIRES REVIEW is the 'else' part of the logic test.


This IIF statement then supplies the query with a new field of values: STAGE 1, STAGE 2, STAGE 3, STAGE 4, REQUIRES REVIEW, and COMPLETE.

In my criteria, I then have

Like "*" & [Forms]![MainNavigation]![NavigationSubform].[Form]![ProjectStatus] & "*" Or Is Null

This is linked to my unbounded form that has a drop down of STAGE 1, STAGE 2, STAGE 3, STAGE 4, REQUIRES REVIEW, and COMPLETE.

It works fine when parsing through these, but I want to give the user to choose NOT COMPLETE in the drop-down as well. NOT COMPLETE is basically just saying anything but COMPLETE. However, since my query does not specify a "NOT COMPLETE" value, how do I set this up in my criteria to work?

[June7]

Need a "Not Complete" value in the calculated field.

I still don't know what condition must exist to result in "Not Complete". The expression you have already considers whether fields have something other than "Complete".

[McArthurGDM]

Actually,

A nice little non-elegant work around that will have to suffice is to just specify

"Not Complete - Stage 1", "Not Complete - Stage 2", "Not Complete - Stage 3", "Not Complete - Stage 4", "Finished", "Not Complete - Requires Review"

Then, with the LIKE function, I can specify NOT COMPLETE, STAGE 1, FINISHED, etc... etc..