IIF statement not returning correct results (1 Viewer)

I have an IIF statement I am trying to use in a report.

It reads:


This worked great in Crystal (in Crystal format), but isn't working in SSRS. No matter how I assemble this statement, it seems to ignore the No Groups Value and returns a list of all the users in the system who are active. I've tried everything. Does anyone have a clue? :banghead:

I find it better to do these IIFs in the query. Let it do all the work,
then the report need only show it.

IIF(([NOGROUPS]=1 and [maximousers] = 1 and [UserStatus] = "ACTIVE") ,"2 - Only in default groups - May need ticket to Sys Admins to remove", "false value here")

I've been debating putting this in the query. Also, I probably should have added that this is a very long IIF statement---this is the part that isn't working. It's something I am using to group a user license report.


=IIf(FIELDS!maximousers.Value >= 1 and IsNothing(FIELDS!UserId.Value),
"1 - In maximousers group (AD) ONLY - May need ticket to Sys Admins to remove",

IIF((FIELDS!NOGROUPS.Value >=1 and FIELDS!maximousers.Value = 1) and FIELDS!UserStatus.Value = "ACTIVE",
"2 - Only in default groups - May need ticket to Sys Admins to remove",

IIf(IsNothing(FIELDS!maximousers.Value) and NOT isNothing(FIELDS!UserId.Value) and FIELDS!UserStatus.Value="ACTIVE",
"3 - In Maximo but not maximousers - no AD account",

IIf(FIELDS!UserStatus.Value="JOBCHANGE",
"4 - Job Change - Check with user to see if Maximo account is still needed",

IIf((Fields!UserStatus.Value="SEPARATE" or Fields!UserStatus.Value="DELETED"),
"5 - Separated or Deleted - Delete these from Maximo and backend",

IIf(FIELDS!UserStatus.Value= "INACTIVE" and CStr(FIELDS!HRId.Value)<>CStr(FIELDS!PersonId.Value) and (FIELDS!EmployeeType.Value="B" or Fields!EmployeeType.Value="NB"), "6 - Inactive users with no HR account - these may be separations",

IIf(FIELDS!UserStatus.Value = "NEWREG" or Fields!UserStatus.Value="BLOCKED",
"6 - Blocked or NewReg Status",

IIf(FIELDS!UserStatus.Value= "INACTIVE" and FIELDS!UserStatusDate.Value <DateAdd("M", -3, Today()), "7 - Inactivated more than 3 months - Maybe delete these", "8 - No issues found"))))))))

Yes it would have been helpful to post the whole expression in the first place.
Ranman has already shown you how to modify the first section
a) Remove Fields!
b) Remove .Value

You could do the same for each part in turn
BUT nested IIf statements are a clumsy method of coding and its very easy to make mistakes

If its not already part of a query then I suggest you create a function which gives the required output based on a series of Select Case or If ...ElseIf...End if statements.
TEST it carefully with a variety of values to check it works
Then use the function in your query or SQL statement

Yes---thank you. I am probably not the first person to not post the full content. I was thinking it may have to go to a case statement. And you can't remove FIELD and VALUE because this is an SSRS expression.

jscgov said:

I am probably not the first person to not post the full content.

Click to expand...

Yes, it happens all too frequently.

"I have an error with my code" is another, without stating what the error is.

"It doesn't work" is also a great favourite.

Nor will you be the last
Excuse my ignorance, but why can't you remove .Value which is the default property in Access.
Is FIELDS the name of the table or, as Ranman and I both assumed, a reference to field names...
I'm not sure why SSRS is relevant here.

@Colin - from memory .Value is used in SSRS to recover the field value. I seem to recall seeing it appear on it's own.

Hi Minty
That may be so but as this is being done in Access, why would that matter?
After all although the wildcard in SS is %, using a wildcard on a SS linked table in Access normally uses * …. unless of course ANSI92 mode has been set.

EDIT
Ignore the above
Sorry just re-Read post #1 which also says SSRS.
As this is in Queries forum, I assumed Access