IIF Statement Problem

C

cc143most

This is a continuation of my question from 2 days ago. I have a field
"Consent Status" that has data strings such as "Declined by Fam - PKO",
"Declined by Fam - Hosp Approach", "Declined by Us - Rule Out", "Declined by
Us - Can't Retrieve", "Obtained by Us", "Obtained by Hosp", and more. I need
to keep this field as is for some reporting; however, I need to be able to
total all of the subgroups of each "Declined by Fam", "Declined by Us",
"Obtained" for this specific report. I don't need the detail as to why, just
which group it falls under.

I have created the following IIF statement to give me another column; but, I
only get blanks. Can anyone tell me why?

IIf([Consent Status]="Declined by Fam*","Declined",IIf([Consent
Status]="Declined by Us*","RO",IIf([Consent Status]="Obtained*","Obtained")))
AS Consent

Thank you!
 
K

KARL DEWEY

Try this --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)],
Sum(IIf(Left([Consent Status],InStr([Consent Status]," ")-1)="Declined",1,0))
AS [Total Declined], Sum(IIf(Left([Consent Status],InStr([Consent Status],"
")-1)="Obtained",1,0)) AS [Total Obtained]
FROM [Jan 2009 Query]
GROUP BY [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)]
PIVOT [Jan 2009 Query].[Consent Status];
 
M

Marshall Barton

cc143most said:
This is a continuation of my question from 2 days ago. I have a field
"Consent Status" that has data strings such as "Declined by Fam - PKO",
"Declined by Fam - Hosp Approach", "Declined by Us - Rule Out", "Declined by
Us - Can't Retrieve", "Obtained by Us", "Obtained by Hosp", and more. I need
to keep this field as is for some reporting; however, I need to be able to
total all of the subgroups of each "Declined by Fam", "Declined by Us",
"Obtained" for this specific report. I don't need the detail as to why, just
which group it falls under.

I have created the following IIF statement to give me another column; but, I
only get blanks. Can anyone tell me why?

IIf([Consent Status]="Declined by Fam*","Declined",IIf([Consent
Status]="Declined by Us*","RO",IIf([Consent Status]="Obtained*","Obtained")))
AS Consent
Click to expand...


When you use wildcards, you have to use the Like operator
instead of =
IIf([Consent Status] Like "Declined by Fam*", ...
 
C

cc143most

Karl,

Thank you for your response. I tried this and I keep getting errors. I
simply cut and pasted, replacing [Jan 2009 Query] with [Qry - Orig Data w/
Hosp Add Step 2]. (Long story but started over trying to reduce the number
of steps here).

Also, I need the totals to be in 3 separate categories. "Declined by Family
= Declined"; "Declined by Us = RO", "Obtained (no matter how) = Obtained". I
was able to use my IIF statement yesterday (one at a time) and it worked. I
just can't figure out why it won't when I combine it. And, yes, my next step
would be a crosstab from this. If I can do it in one step, it would be
wonderful!

Thanks again for your time and help!

TRANSFORM Count([Qry - Orig Data w/ Hosp Add Step 2].[Link #]) AS
[CountOfLink #]
SELECT [Qry - Orig Data w/ Hosp Add Step 2].Branch, [Qry - Orig Data w/ Hosp
Add Step 2].[Hospital (State)],
Sum(IIf(Left([Consent Status],InStr([Consent Status]," ")-1)="Declined",1,0))
AS [Total Declined], Sum(IIf(Left([Consent Status],InStr([Consent Status],"
")-1)="Obtained",1,0)) AS [Total Obtained]
FROM [Qry - Orig Data w/ Hosp Add Step 2]
GROUP BY [Qry - Orig Data w/ Hosp Add Step 2].Branch, [Qry - Orig Data w/
Hosp Add Step 2].[Hospital (State)]
PIVOT [Qry - Orig Data w/ Hosp Add Step 2].[Consent Status];

KARL DEWEY said:
Try this --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)],
Sum(IIf(Left([Consent Status],InStr([Consent Status]," ")-1)="Declined",1,0))
AS [Total Declined], Sum(IIf(Left([Consent Status],InStr([Consent Status],"
")-1)="Obtained",1,0)) AS [Total Obtained]
FROM [Jan 2009 Query]
GROUP BY [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)]
PIVOT [Jan 2009 Query].[Consent Status];


cc143most said:
This is a continuation of my question from 2 days ago. I have a field
"Consent Status" that has data strings such as "Declined by Fam - PKO",
"Declined by Fam - Hosp Approach", "Declined by Us - Rule Out", "Declined by
Us - Can't Retrieve", "Obtained by Us", "Obtained by Hosp", and more. I need
to keep this field as is for some reporting; however, I need to be able to
total all of the subgroups of each "Declined by Fam", "Declined by Us",
"Obtained" for this specific report. I don't need the detail as to why, just
which group it falls under.

I have created the following IIF statement to give me another column; but, I
only get blanks. Can anyone tell me why?

IIf([Consent Status]="Declined by Fam*","Declined",IIf([Consent
Status]="Declined by Us*","RO",IIf([Consent Status]="Obtained*","Obtained")))
AS Consent

Thank you!
Click to expand...
Click to expand...
 
C

cc143most

Marsh,

Thank you, thank you, THANK YOU! This fixed it and it's beautiful! Missing
little things like this is what happens when you are learning as you go.

Thanks again so very much!

Marshall Barton said:
cc143most said:
This is a continuation of my question from 2 days ago. I have a field
"Consent Status" that has data strings such as "Declined by Fam - PKO",
"Declined by Fam - Hosp Approach", "Declined by Us - Rule Out", "Declined by
Us - Can't Retrieve", "Obtained by Us", "Obtained by Hosp", and more. I need
to keep this field as is for some reporting; however, I need to be able to
total all of the subgroups of each "Declined by Fam", "Declined by Us",
"Obtained" for this specific report. I don't need the detail as to why, just
which group it falls under.

I have created the following IIF statement to give me another column; but, I
only get blanks. Can anyone tell me why?

IIf([Consent Status]="Declined by Fam*","Declined",IIf([Consent
Status]="Declined by Us*","RO",IIf([Consent Status]="Obtained*","Obtained")))
AS Consent
Click to expand...


When you use wildcards, you have to use the Like operator
instead of =
IIf([Consent Status] Like "Declined by Fam*", ...
Click to expand...
 
D

Duane Hookom

I would add a field to your status lookup table with the alternative text
values. You should be implementing your logic in data rather than
expressions.

--
Duane Hookom
Microsoft Access MVP


KARL DEWEY said:
Try this --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)],
Sum(IIf(Left([Consent Status],InStr([Consent Status]," ")-1)="Declined",1,0))
AS [Total Declined], Sum(IIf(Left([Consent Status],InStr([Consent Status],"
")-1)="Obtained",1,0)) AS [Total Obtained]
FROM [Jan 2009 Query]
GROUP BY [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)]
PIVOT [Jan 2009 Query].[Consent Status];


cc143most said:
This is a continuation of my question from 2 days ago. I have a field
"Consent Status" that has data strings such as "Declined by Fam - PKO",
"Declined by Fam - Hosp Approach", "Declined by Us - Rule Out", "Declined by
Us - Can't Retrieve", "Obtained by Us", "Obtained by Hosp", and more. I need
to keep this field as is for some reporting; however, I need to be able to
total all of the subgroups of each "Declined by Fam", "Declined by Us",
"Obtained" for this specific report. I don't need the detail as to why, just
which group it falls under.

I have created the following IIF statement to give me another column; but, I
only get blanks. Can anyone tell me why?

IIf([Consent Status]="Declined by Fam*","Declined",IIf([Consent
Status]="Declined by Us*","RO",IIf([Consent Status]="Obtained*","Obtained")))
AS Consent

Thank you!
Click to expand...
Click to expand...
 
K

KARL DEWEY

Thank you for your response. I tried this and I keep getting errors.

You did not say what the errors were.

cc143most said:
Karl,

Thank you for your response. I tried this and I keep getting errors. I
simply cut and pasted, replacing [Jan 2009 Query] with [Qry - Orig Data w/
Hosp Add Step 2]. (Long story but started over trying to reduce the number
of steps here).

Also, I need the totals to be in 3 separate categories. "Declined by Family
= Declined"; "Declined by Us = RO", "Obtained (no matter how) = Obtained". I
was able to use my IIF statement yesterday (one at a time) and it worked. I
just can't figure out why it won't when I combine it. And, yes, my next step
would be a crosstab from this. If I can do it in one step, it would be
wonderful!

Thanks again for your time and help!

TRANSFORM Count([Qry - Orig Data w/ Hosp Add Step 2].[Link #]) AS
[CountOfLink #]
SELECT [Qry - Orig Data w/ Hosp Add Step 2].Branch, [Qry - Orig Data w/ Hosp
Add Step 2].[Hospital (State)],
Sum(IIf(Left([Consent Status],InStr([Consent Status]," ")-1)="Declined",1,0))
AS [Total Declined], Sum(IIf(Left([Consent Status],InStr([Consent Status],"
")-1)="Obtained",1,0)) AS [Total Obtained]
FROM [Qry - Orig Data w/ Hosp Add Step 2]
GROUP BY [Qry - Orig Data w/ Hosp Add Step 2].Branch, [Qry - Orig Data w/
Hosp Add Step 2].[Hospital (State)]
PIVOT [Qry - Orig Data w/ Hosp Add Step 2].[Consent Status];

KARL DEWEY said:
Try this --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)],
Sum(IIf(Left([Consent Status],InStr([Consent Status]," ")-1)="Declined",1,0))
AS [Total Declined], Sum(IIf(Left([Consent Status],InStr([Consent Status],"
")-1)="Obtained",1,0)) AS [Total Obtained]
FROM [Jan 2009 Query]
GROUP BY [Jan 2009 Query].Branch, [Jan 2009 Query].[Hospital (State)]
PIVOT [Jan 2009 Query].[Consent Status];


cc143most said:
This is a continuation of my question from 2 days ago. I have a field
"Consent Status" that has data strings such as "Declined by Fam - PKO",
"Declined by Fam - Hosp Approach", "Declined by Us - Rule Out", "Declined by
Us - Can't Retrieve", "Obtained by Us", "Obtained by Hosp", and more. I need
to keep this field as is for some reporting; however, I need to be able to
total all of the subgroups of each "Declined by Fam", "Declined by Us",
"Obtained" for this specific report. I don't need the detail as to why, just
which group it falls under.

I have created the following IIF statement to give me another column; but, I
only get blanks. Can anyone tell me why?

IIf([Consent Status]="Declined by Fam*","Declined",IIf([Consent
Status]="Declined by Us*","RO",IIf([Consent Status]="Obtained*","Obtained")))
AS Consent

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

cc143most said:
Marsh,

Thank you, thank you, THANK YOU! This fixed it and it's beautiful! Missing
little things like this is what happens when you are learning as you go.

Thanks again so very much!
Click to expand...


OK, now that we've straightened out the syntax, you should
seriously consider Duane's suggestion of putting that stuff
in a table. That way you can just join to the table and
include the reporting group field in the query instead of
using an expression that contains embedded data values.

A big reason why this is a good idea is you can just edit
the table when the time comes when you need to change or add
the text values.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF Statement not working as expected 3
Report Field Problem 3
Basic IIf question 5
help with a query syntax and grouping 1
TO FIND TOTAL ORDERS SHIPPED AND NOT SHIPPED AND TOTAL OF AMOUNT WHICH WERE SHIPPED AND WHICH WERE N 1
Busted IIf function 3
IIf problem with <> 10
IIf statement not working 4

Top