# "data type mismatch"

D

#### Daniel Collison

A field - "Q1" - in a table stores values 1 through 5. A query includes a
calculated field -"Q_1" - where the number "1" is returned if the value in Q1
is greater than or equal to 4. The calculated field is formatted as a
general number. The sql for the query follows:

SELECT IIf([Q1]>=4,"1","") AS [Q 1]
FROM [tbl CSR Findings]
WHERE ((([tbl CSR Findings].LL)=1));

If I try to use the sum function on the Q_1 field in the "Total" row of the
query, the following error message is returned: "Data type mismatch in
criteria expression."

Any thoughts on how to correct for this problem?

E

#### Emma Hope

Hi Daniel,

Take the inverted commas from the iif statement. "1" makes it think it is 1
stored as text not a number also use 0 or something else instead of "" as it
also think this is text albeit a blank value and as you know it cannot sum
what it thinks are text values......

HTH

Emma

M

#### MikeJohnB

If I have got what you are trying to do wrong, I apologise in advance

I have the following SQL

SELECT Count([tbl CSR Findings].Q1) AS CountOfQ1
FROM [tbl CSR Findings]
WHERE (((IIf([Q1]>=4,1,0))=1));

I think you want to return the total number of Q1 records that are = to or
Greater than 4, I have set the totals the same as you but instead of sum Q_1
I have counted Q1, this returns a value of 2 where I have four records with

4
3
5
2

Data.

Like I say, if I have mis-understood what you are trying to, sorry.

Kindest Regards

Mike B

D

#### Daniel Collison

THANKS! Your suggested SQL is definitely a more elegant solution to the
problem.

MikeJohnB said:
If I have got what you are trying to do wrong, I apologise in advance

I have the following SQL

SELECT Count([tbl CSR Findings].Q1) AS CountOfQ1
FROM [tbl CSR Findings]
WHERE (((IIf([Q1]>=4,1,0))=1));

I think you want to return the total number of Q1 records that are = to or
Greater than 4, I have set the totals the same as you but instead of sum Q_1
I have counted Q1, this returns a value of 2 where I have four records with

4
3
5
2

Data.

Like I say, if I have mis-understood what you are trying to, sorry.

Kindest Regards

Mike B
--
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Daniel Collison said:
A field - "Q1" - in a table stores values 1 through 5. A query includes a
calculated field -"Q_1" - where the number "1" is returned if the value in Q1
is greater than or equal to 4. The calculated field is formatted as a
general number. The sql for the query follows:

SELECT IIf([Q1]>=4,"1","") AS [Q 1]
FROM [tbl CSR Findings]
WHERE ((([tbl CSR Findings].LL)=1));

If I try to use the sum function on the Q_1 field in the "Total" row of the
query, the following error message is returned: "Data type mismatch in
criteria expression."

Any thoughts on how to correct for this problem?

D

#### Daniel Collison

Chris O'C via AccessMonster.com said:
Jet can't sum words, only numbers. Use numbers and null instead of strings
for the calculated field.

SELECT IIf([Q1]>=4,1,NULL) AS [Q 1]

Chris

Daniel said:
A field - "Q1" - in a table stores values 1 through 5. A query includes a
calculated field -"Q_1" - where the number "1" is returned if the value in Q1
is greater than or equal to 4. The calculated field is formatted as a
general number. The sql for the query follows:

SELECT IIf([Q1]>=4,"1","") AS [Q 1]
FROM [tbl CSR Findings]
WHERE ((([tbl CSR Findings].LL)=1));

If I try to use the sum function on the Q_1 field in the "Total" row of the
query, the following error message is returned: "Data type mismatch in
criteria expression."

Any thoughts on how to correct for this problem?

D

G

#### Guest

Chris O'C via AccessMonster.com said:
Jet can't sum words, only numbers. Use numbers and null instead of
strings
for the calculated field.

SELECT IIf([Q1]>=4,1,NULL) AS [Q 1]

Chris

Daniel said:
A field - "Q1" - in a table stores values 1 through 5. A query includes a
calculated field -"Q_1" - where the number "1" is returned if the value in
Q1
is greater than or equal to 4. The calculated field is formatted as a
general number. The sql for the query follows:

SELECT IIf([Q1]>=4,"1","") AS [Q 1]
FROM [tbl CSR Findings]
WHERE ((([tbl CSR Findings].LL)=1));

If I try to use the sum function on the Q_1 field in the "Total" row of
the
query, the following error message is returned: "Data type mismatch in
criteria expression."

Any thoughts on how to correct for this problem?