IF/THEN statement problem

[sfgiantsdude]

A bit of a complicated issue: I wrote this statement to test whether a row should be given a value under the Expr5 column of "TEST or "EXCLUDE". I only want this if/then statement to apply to Q42011 and beyond (hence the exclusion of 113-101) but I'm getting "EXCLUDE" values to rows that are within the Q12010-Q32011 when I really don't even want them to be included in my IF/THEN column. I'd rather they show up blank. Any suggestions?



Expr5: IIf(([MCA_EPM_ISSUES]![KP_YR_NB] & Right([MCA_EPM_ISSUES]![QTR],2)) Not In ("113","112","111","104","103","102","101") And [REF - Department V2]![REPORTING_DEPT] In ("LABORATORY","RADIOLOGY") And [MCA_EPM_ISSUES]![SUB_DEPT] Like "*HOV*","TEST","EXCLUDE")

[RayMilhon]

The way the IIF statement is written gives the following results

If Not in 113-101 and reporting dept in lab radiology and subdept like *HOV* Then
Test
Else
Exclude
End if

From your description what your trying to get is

If Not in 113-101 Then
If reportingdept in lab,radiology and subdept like HOV Then
"Test"
Else
"EXCLUDE"
End if
Else
""
End if

so your statement should be
Expr5: IIf(([MCA_EPM_ISSUES]![KP_YR_NB] & Right([MCA_EPM_ISSUES]![QTR],2)) Not In ("113","112","111","104","103","102","101") ,IIF( [REF - Department V2]![REPORTING_DEPT] In ("LABORATORY","RADIOLOGY") And [MCA_EPM_ISSUES]![SUB_DEPT] Like "*HOV*","TEST","EXCLUDE"),"")

[jzwp11]

Could you provide some of the data that are in the two fields [KP_YR_NB] and [QTR] as an example? I'm having a hard time understanding how you get from something like Q42011 to 113, 112 etc.

[sfgiantsdude]

KP_YR_NB has data related to year, like "2011" "2010" "2009" etc. The QTR variable just has the quarterly number whether it be 1,2,3,4. So the concatenation of the two should produce 113, 112, 104, etc. However I just noticed that I did the right cutoff for the wrong variable so I need to fix that.

[sfgiantsdude]

RayMilhon: That worked! Thanks very much for your assistance. It's greatly appreciated!

[June7]

Might help to show sample data. Why are you concatenating and using Right - Q42011 is not a single field value? Why not simply: [fieldname] > "Q42011"?

You have created an expression that returns a value for each and every record, now you can use value to filter records. The filter criteria would be: "TEST".

Alternatively, break this up to show criteria under each of the fields.

EDIT: I did not see other posts before sending mine. Glad you found solution.