"data type mismatch"

[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?

 

[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

 

[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

 

[Daniel Collison]

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

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
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

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?

 

[Daniel Collison]

thanks...this was very helpful

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

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?

 

[Daniel Collison]

Thanks...this was very helpful! DC

 

[Guest]

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

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?