"data type mismatch"

  • Thread starter Daniel Collison
  • Start date

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

Advertisements

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


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

thanks...this was very helpful

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

Advertisements

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?
 

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

Top