Problems with IIf()

[John S. Ford, MD]

I created a field within a query with the following (in the query design
grid):


BPControl1: IIf(Count([BPControl])=0, "N/A",
Format(100*Abs(Sum([BPControl]=1))/(Count([BPControl])),"0.0") & "%")


BPControl is a field in this query's datasource (also a query) that is an
integer (1, 2 or 3) representing the degree to which a patient's blood
pressure is under control. What I'm trying to do is calculate the
percentage of patients with a BPControl value of 1. (I will also have a
BPControl2 and BPControl3 field.)


The problem is when I run this query, I get the following error message:


"Datatype mismatch in criteria expression."


I assumed that this message arises from the Count([BPControl])=0 clause but
it persists even if I replace it with "True". If I remove this field
completely from the query, the rest of the query works fine with no errors.


Any ideas as to why this is happening? I don't even know where to start in
troubleshooting this. I've not had too many problems with IIf() statements
before (even within the same query).


John

 

[Allen Browne]

Try:
BPControl1: IIf(Count([BPControl])=0, Null,
Abs(Sum([BPControl]=1)) / (Count([BPControl]))

If that works, you can right-click the field (in query design), and choose
Properties.
Set the Format property to:
Percent
or if you prefer:
0.0%

"N/A" is s string (not a number.) Similarly the output from Format() is a
string value. These things will both prevent you from performing math
operations on the field. The field above should output as a number (showing
right-aligned when you view the query datasheet), not text.

If this is for a report, it might be easier to use a Crosstab query where
BPControl is the Column Heading and a Count of BPControl is the Value. In a
report, you could then show the values as a percentage of the total.

 

[John S. Ford, MD]

Allen,

Thanks for trying to help!

I tried the first part of your solution and still get the same error
message. After a little more testing, I've found that the real problem
seems to be that my query doesn't like the

Count([BPControl])=0) clause

or the

Sum([BPControl]=1)/Count([BPControl]) clause

If I replace the IIf conditional statement with "False" and the false clause
with

Format(Abs(-15)/29, "0.0%") (in other words replacing Sum([BPControl])=1 and
Count([BPControl]) with random test numbers, it works as expected i.e.:

BPControl: IIf(False, "N/A", Format(Abs(-15)/29, "0.0%"))

So it doesn't appear to be a problem with mixing integers and strings. I
have very similar code in other parts of the same query but unlike the
BPControl field, these other calculated fields use Boolean fields relying on
directly entered data. BPControl is itself calculated in another query.
Could the problem have something to do with the nature of the BPControl
field? This is really baffling to me!

John

Try:
BPControl1: IIf(Count([BPControl])=0, Null,
Abs(Sum([BPControl]=1)) / (Count([BPControl]))

If that works, you can right-click the field (in query design), and choose
Properties.
Set the Format property to:
Percent
or if you prefer:
0.0%

"N/A" is s string (not a number.) Similarly the output from Format() is a
string value. These things will both prevent you from performing math
operations on the field. The field above should output as a number (showing
right-aligned when you view the query datasheet), not text.

If this is for a report, it might be easier to use a Crosstab query where
BPControl is the Column Heading and a Count of BPControl is the Value. In a
report, you could then show the values as a percentage of the total.

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

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

I created a field within a query with the following (in the query design
grid):

BPControl1: IIf(Count([BPControl])=0, "N/A",
Format(100*Abs(Sum([BPControl]=1))/(Count([BPControl])),"0.0") & "%")

BPControl is a field in this query's datasource (also a query) that is an
integer (1, 2 or 3) representing the degree to which a patient's blood
pressure is under control. What I'm trying to do is calculate the
percentage of patients with a BPControl value of 1. (I will also have a
BPControl2 and BPControl3 field.)

The problem is when I run this query, I get the following error message:

"Datatype mismatch in criteria expression."

I assumed that this message arises from the Count([BPControl])=0 clause
but
it persists even if I replace it with "True". If I remove this field
completely from the query, the rest of the query works fine with no
errors.


Any ideas as to why this is happening? I don't even know where to start
in
troubleshooting this. I've not had too many problems with IIf()
statements
before (even within the same query).

 

[John S. Ford, MD]

In fact, as I just now discovered, simply entering:

BPControl1: Count(BPControl)

gives the same error message! So there must be something unique about the
BPControl field. Any ideas?

John

 

[Allen Browne]

You could try typecasting the field produced by the other query, by wraping
the calculation in Int() or CLng() or something.

But Count(), Sum(), etc work in limited contexts, so they may not be
applicable to what you are doing in this query.

Any chance of using the Crosstab to get the results?

Alternatively, DSum(), DCount() etc might work.

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

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

Allen,

Thanks for trying to help!

I tried the first part of your solution and still get the same error
message. After a little more testing, I've found that the real problem
seems to be that my query doesn't like the

Count([BPControl])=0) clause

or the

Sum([BPControl]=1)/Count([BPControl]) clause

If I replace the IIf conditional statement with "False" and the false
clause
with

Format(Abs(-15)/29, "0.0%") (in other words replacing Sum([BPControl])=1
and
Count([BPControl]) with random test numbers, it works as expected i.e.:

BPControl: IIf(False, "N/A", Format(Abs(-15)/29, "0.0%"))

So it doesn't appear to be a problem with mixing integers and strings. I
have very similar code in other parts of the same query but unlike the
BPControl field, these other calculated fields use Boolean fields relying
on
directly entered data. BPControl is itself calculated in another query.
Could the problem have something to do with the nature of the BPControl
field? This is really baffling to me!

John

Try:
BPControl1: IIf(Count([BPControl])=0, Null,
Abs(Sum([BPControl]=1)) / (Count([BPControl]))

If that works, you can right-click the field (in query design), and
choose
Properties.
Set the Format property to:
Percent
or if you prefer:
0.0%

"N/A" is s string (not a number.) Similarly the output from Format() is a
string value. These things will both prevent you from performing math
operations on the field. The field above should output as a number (showing
right-aligned when you view the query datasheet), not text.

If this is for a report, it might be easier to use a Crosstab query where
BPControl is the Column Heading and a Count of BPControl is the Value. In a
report, you could then show the values as a percentage of the total.

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

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

I created a field within a query with the following (in the query design
grid):

BPControl1: IIf(Count([BPControl])=0, "N/A",
Format(100*Abs(Sum([BPControl]=1))/(Count([BPControl])),"0.0") & "%")

BPControl is a field in this query's datasource (also a query) that is an
integer (1, 2 or 3) representing the degree to which a patient's blood
pressure is under control. What I'm trying to do is calculate the
percentage of patients with a BPControl value of 1. (I will also have
a
BPControl2 and BPControl3 field.)

The problem is when I run this query, I get the following error
message:

"Datatype mismatch in criteria expression."

I assumed that this message arises from the Count([BPControl])=0 clause
but
it persists even if I replace it with "True". If I remove this field
completely from the query, the rest of the query works fine with no
errors.


Any ideas as to why this is happening? I don't even know where to
start
in
troubleshooting this. I've not had too many problems with IIf()
statements
before (even within the same query).

 

[John S. Ford, MD]

I guess I'll have to try something else. But I have no idea why the
aggregate functions treat this field differently. The original query that
calculates BPControl works perfectly, produces an integer (which shows up
right-justified in the datasheet view) and doesn't seem any different from
other fields for which I'm having no problems.

Well thanks for trying Allen!

John

You could try typecasting the field produced by the other query, by wraping
the calculation in Int() or CLng() or something.

But Count(), Sum(), etc work in limited contexts, so they may not be
applicable to what you are doing in this query.

Any chance of using the Crosstab to get the results?

Alternatively, DSum(), DCount() etc might work.

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

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

Allen,

Thanks for trying to help!

I tried the first part of your solution and still get the same error
message. After a little more testing, I've found that the real problem
seems to be that my query doesn't like the

Count([BPControl])=0) clause

or the

Sum([BPControl]=1)/Count([BPControl]) clause

If I replace the IIf conditional statement with "False" and the false
clause
with

Format(Abs(-15)/29, "0.0%") (in other words replacing Sum([BPControl])=1
and
Count([BPControl]) with random test numbers, it works as expected i.e.:

BPControl: IIf(False, "N/A", Format(Abs(-15)/29, "0.0%"))

So it doesn't appear to be a problem with mixing integers and strings. I
have very similar code in other parts of the same query but unlike the
BPControl field, these other calculated fields use Boolean fields relying
on
directly entered data. BPControl is itself calculated in another query.
Could the problem have something to do with the nature of the BPControl
field? This is really baffling to me!

John

Try:
BPControl1: IIf(Count([BPControl])=0, Null,
Abs(Sum([BPControl]=1)) / (Count([BPControl]))

If that works, you can right-click the field (in query design), and
choose
Properties.
Set the Format property to:
Percent
or if you prefer:
0.0%

"N/A" is s string (not a number.) Similarly the output from Format() is a
string value. These things will both prevent you from performing math
operations on the field. The field above should output as a number (showing
right-aligned when you view the query datasheet), not text.

If this is for a report, it might be easier to use a Crosstab query where
BPControl is the Column Heading and a Count of BPControl is the Value.

In
a

report, you could then show the values as a percentage of the total.

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

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

I created a field within a query with the following (in the query design
grid):

BPControl1: IIf(Count([BPControl])=0, "N/A",
Format(100*Abs(Sum([BPControl]=1))/(Count([BPControl])),"0.0") & "%")

BPControl is a field in this query's datasource (also a query) that

is
an

integer (1, 2 or 3) representing the degree to which a patient's blood
pressure is under control. What I'm trying to do is calculate the
percentage of patients with a BPControl value of 1. (I will also have
a
BPControl2 and BPControl3 field.)

The problem is when I run this query, I get the following error
message:

"Datatype mismatch in criteria expression."

I assumed that this message arises from the Count([BPControl])=0 clause
but
it persists even if I replace it with "True". If I remove this field
completely from the query, the rest of the query works fine with no
errors.


Any ideas as to why this is happening? I don't even know where to
start
in
troubleshooting this. I've not had too many problems with IIf()
statements
before (even within the same query).

 

[Van T. Dinh]

It is possible that the Query is corrupted??? I don't have any problem
using expressions you and Allan posted.

Just try re-creating the Query by opening a new Query and re-typing the SQL
(don't copy and poste) and see if it works ...

(I assume the database is compacted & repaired regularly???)

 

[User]

Your original expression seems to work for me too, although when I change
the type of the BPControl field from number to text I get the error you
mention (even if I only have numeric values in the field).

In fact, as I just now discovered, simply entering:

BPControl1: Count(BPControl)

gives the same error message! So there must be something unique about the
BPControl field. Any ideas?

John

Allen,

Thanks for trying to help!

I tried the first part of your solution and still get the same error
message. After a little more testing, I've found that the real problem
seems to be that my query doesn't like the

Count([BPControl])=0) clause

or the

Sum([BPControl]=1)/Count([BPControl]) clause

If I replace the IIf conditional statement with "False" and the false clause
with

Format(Abs(-15)/29, "0.0%") (in other words replacing Sum([BPControl])=1 and
Count([BPControl]) with random test numbers, it works as expected i.e.:

BPControl: IIf(False, "N/A", Format(Abs(-15)/29, "0.0%"))

So it doesn't appear to be a problem with mixing integers and strings. I
have very similar code in other parts of the same query but unlike the
BPControl field, these other calculated fields use Boolean fields relying on
directly entered data. BPControl is itself calculated in another query.
Could the problem have something to do with the nature of the BPControl
field? This is really baffling to me!

John