IIf AND

[AccessNeophyte]

Greetings All,

I have a query with these 2 expressions:
Expr2: IIf([Pregnant?]="Yes","1","0")
Expr3: (IIf([IVDrugUser?]=True,"1","0"))

For the report I'm trying to create, I need to know how many
[Pregnant?]=Yes" are ALSO [IVDrugUser]=True."
In other words, where both expressions return "1"

How do I get there from here? Do I make another expression in the
query? Do I put an exprerssion in the control on the report?

Thanks in advance!!!
Access Neophyte

 

[Tom Lake]

Greetings All,

I have a query with these 2 expressions:
Expr2: IIf([Pregnant?]="Yes","1","0")
Expr3: (IIf([IVDrugUser?]=True,"1","0"))

For the report I'm trying to create, I need to know how many
[Pregnant?]=Yes" are ALSO [IVDrugUser]=True."
In other words, where both expressions return "1"

How do I get there from here? Do I make another expression in the
query? Do I put an exprerssion in the control on the report?

Put the expression

="1"

in the Criteria line of both Expr1 and Expr2

Tom Lake

 

[Allen Browne]

You can put multiple conditions together with AND, but there's a problem
here with data types.

Is ([Pregnant?] a Yes/No field?
If so, test for True, not the text "Yes".

Presumably you want to count the results, so don't put quotes around the 1
and 0. (Quotes are for text, and it may no sum correctly, e.g. "1" + "1"
might give you 11 instead of 2.)

Try something like this:
Expr4: IIf(([Pregnant?]) AND (([IVDrugUser?]), 1, 0)

 

[AccessNeophyte]

Thanks Guys!

Allen,
[Pregnant?] is a Combo Box with 4 possible responses (Yes, No,
Unknown, and Male Client), ergo the quote marks. That's what is
making this more difficult.

Tom,
Won't putting ="1" in the Criteria line limit the records to only
those with a "1" in both fields? The report I'm creating from this
query calls for several totals for the group of clients who were here
in the report month, of which [Pregnant?] and [IVDrugUser?] are only
2. I don't want to eliminate (not see) the records where these are
not "1", I just want to count the instances where they are.

All,
I tried this expression:
IIf(([Pregnant?]="Yes") AND ([IVDrugUser?] = True)), "1","0"
but no luck. The message I get says that "The expression you entered
has a function containing the wrong number of arguments."

I had one client who meets both criteria (sad, I know), so that is my
"test case."

Am I going to enter the expression in the query, or is it possibly
best to enter it as the Control Source of a text box on the Report?
sigh...

You can put multiple conditions together with AND, but there's a problem
here with data types.

Is ([Pregnant?] a Yes/No field?
If so, test for True, not the text "Yes".

Presumably you want to count the results, so don't put quotes around the 1
and 0. (Quotes are for text, and it may no sum correctly, e.g. "1" + "1"
might give you 11 instead of 2.)

Try something like this:
Expr4: IIf(([Pregnant?]) AND (([IVDrugUser?]), 1, 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Greetings All,

I have a query with these 2 expressions:
Expr2: IIf([Pregnant?]="Yes","1","0")
Expr3: (IIf([IVDrugUser?]=True,"1","0"))

For the report I'm trying to create, I need to know how many
[Pregnant?]=Yes" are ALSO [IVDrugUser]=True."
In other words, where both expressions return "1"

How do I get there from here? Do I make another expression in the
query? Do I put an exprerssion in the control on the report?

Thanks in advance!!!
Access Neophyte- Hide quoted text -

- Show quoted text -

 

[Allen Browne]

You closed the function bracket too early:
IIf(([Pregnant?]="Yes") AND ([IVDrugUser?] = True), 1, 0)

Query is probably better than report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Guys!

Allen,
[Pregnant?] is a Combo Box with 4 possible responses (Yes, No,
Unknown, and Male Client), ergo the quote marks. That's what is
making this more difficult.

Tom,
Won't putting ="1" in the Criteria line limit the records to only
those with a "1" in both fields? The report I'm creating from this
query calls for several totals for the group of clients who were here
in the report month, of which [Pregnant?] and [IVDrugUser?] are only
2. I don't want to eliminate (not see) the records where these are
not "1", I just want to count the instances where they are.

All,
I tried this expression:
IIf(([Pregnant?]="Yes") AND ([IVDrugUser?] = True)), "1","0"
but no luck. The message I get says that "The expression you entered
has a function containing the wrong number of arguments."

I had one client who meets both criteria (sad, I know), so that is my
"test case."

Am I going to enter the expression in the query, or is it possibly
best to enter it as the Control Source of a text box on the Report?
sigh...

You can put multiple conditions together with AND, but there's a problem
here with data types.

Is ([Pregnant?] a Yes/No field?
If so, test for True, not the text "Yes".

Presumably you want to count the results, so don't put quotes around the
1
and 0. (Quotes are for text, and it may no sum correctly, e.g. "1" + "1"
might give you 11 instead of 2.)

Try something like this:
Expr4: IIf(([Pregnant?]) AND (([IVDrugUser?]), 1, 0)

Greetings All,

I have a query with these 2 expressions:
Expr2: IIf([Pregnant?]="Yes","1","0")
Expr3: (IIf([IVDrugUser?]=True,"1","0"))

For the report I'm trying to create, I need to know how many
[Pregnant?]=Yes" are ALSO [IVDrugUser]=True."
In other words, where both expressions return "1"

How do I get there from here? Do I make another expression in the
query? Do I put an exprerssion in the control on the report?

Thanks in advance!!!
Access Neophyte- Hide quoted text -

- Show quoted text -

 

[AccessNeophyte]

You closed the function bracket too early:
IIf(([Pregnant?]="Yes") AND ([IVDrugUser?] = True), 1, 0)

Query is probably better than report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Thanks Guys!

Allen,
[Pregnant?] is a Combo Box with 4 possible responses (Yes, No,
Unknown, and Male Client), ergo the quote marks. That's what is
making this more difficult.

Tom,
Won't putting ="1" in the Criteria line limit the records to only
those with a "1" in both fields? The report I'm creating from this
query calls for several totals for the group of clients who were here
in the report month, of which [Pregnant?] and [IVDrugUser?] are only
2. I don't want to eliminate (not see) the records where these are
not "1", I just want to count the instances where they are.

All,
I tried this expression:
IIf(([Pregnant?]="Yes") AND ([IVDrugUser?] = True)), "1","0"
but no luck. The message I get says that "The expression you entered
has a function containing the wrong number of arguments."

I had one client who meets both criteria (sad, I know), so that is my
"test case."

Am I going to enter the expression in the query, or is it possibly
best to enter it as the Control Source of a text box on the Report?
sigh...

You can put multiple conditions together with AND, but there's a problem
here with data types.
Is ([Pregnant?] a Yes/No field?
If so, test for True, not the text "Yes".
Presumably you want to count the results, so don't put quotes around the
1
and 0. (Quotes are for text, and it may no sum correctly, e.g. "1" + "1"
might give you 11 instead of 2.)
Try something like this:
Expr4: IIf(([Pregnant?]) AND (([IVDrugUser?]), 1, 0)

Greetings All,
I have a query with these 2 expressions:
Expr2: IIf([Pregnant?]="Yes","1","0")
Expr3: (IIf([IVDrugUser?]=True,"1","0"))
For the report I'm trying to create, I need to know how many
[Pregnant?]=Yes" are ALSO [IVDrugUser]=True."
In other words, where both expressions return "1"
How do I get there from here? Do I make another expression in the
query? Do I put an exprerssion in the control on the report?
Thanks in advance!!!
Access Neophyte- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks! It worked!

Now, how do I get the total for this (and the other expressions) into
my Report? Can I do something to the column in the query (like adding
Sum or Count to the expression), and then use the expression as my
control source for a text box in the form? Or should I do the Sum (or
Count?) part in the control on the report? I tried a couple oof
different ways in the expression with no luck.

Thanks,
AccessNeophyte

 

[Allen Browne]

You can sum the expression in the Report Footer:
=Sum((([Pregnant?]="Yes") AND ([IVDrugUser?] = True), 1, 0))

Or, it might be easier to put the expression into the report's source query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You closed the function bracket too early:
IIf(([Pregnant?]="Yes") AND ([IVDrugUser?] = True), 1, 0)

Query is probably better than report.

Allen,
[Pregnant?] is a Combo Box with 4 possible responses (Yes, No,
Unknown, and Male Client), ergo the quote marks. That's what is
making this more difficult.

Tom,
Won't putting ="1" in the Criteria line limit the records to only
those with a "1" in both fields? The report I'm creating from this
query calls for several totals for the group of clients who were here
in the report month, of which [Pregnant?] and [IVDrugUser?] are only
2. I don't want to eliminate (not see) the records where these are
not "1", I just want to count the instances where they are.

All,
I tried this expression:
IIf(([Pregnant?]="Yes") AND ([IVDrugUser?] = True)), "1","0"
but no luck. The message I get says that "The expression you entered
has a function containing the wrong number of arguments."

I had one client who meets both criteria (sad, I know), so that is my
"test case."

Am I going to enter the expression in the query, or is it possibly
best to enter it as the Control Source of a text box on the Report?
sigh...

You can put multiple conditions together with AND, but there's a
problem
here with data types.

Is ([Pregnant?] a Yes/No field?
If so, test for True, not the text "Yes".

Presumably you want to count the results, so don't put quotes around
the
1
and 0. (Quotes are for text, and it may no sum correctly, e.g. "1" +
"1"
might give you 11 instead of 2.)

Try something like this:
Expr4: IIf(([Pregnant?]) AND (([IVDrugUser?]), 1, 0)

Greetings All,

I have a query with these 2 expressions:
Expr2: IIf([Pregnant?]="Yes","1","0")
Expr3: (IIf([IVDrugUser?]=True,"1","0"))

For the report I'm trying to create, I need to know how many
[Pregnant?]=Yes" are ALSO [IVDrugUser]=True."
In other words, where both expressions return "1"

How do I get there from here? Do I make another expression in the
query? Do I put an exprerssion in the control on the report?

Thanks in advance!!!
Access Neophyte- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks! It worked!

Now, how do I get the total for this (and the other expressions) into
my Report? Can I do something to the column in the query (like adding
Sum or Count to the expression), and then use the expression as my
control source for a text box in the form? Or should I do the Sum (or
Count?) part in the control on the report? I tried a couple oof
different ways in the expression with no luck.

Thanks,
AccessNeophyte