Cross Tab - Data Type Mismatch in Criteria Expression

[Access_Help]

Hi,

I am using a calculated field (set to general number) from a query to serve the vAlue field in the crosstab query. I am getting a data type mismatch error.

Is there anything I can do to resolve this?

 

[Ranman256]

TestMark is not numeric.
In the source query, qryMarkGrid,
set the field to CDBL(testMark),
(Or some other numeric conversion )

 

[Minty]

Did you apply any format() to your calculated field to get your Percentage. If you did, it will be formatted as a string, not a number.

 

[almahmood]

Do you have any external criteria in your source wry_mark_grid which is coming direct from a Form? Would be more helpful if you can share that database or both queries.

 

[Access_Help]

TestMark is not numeric.
In the source query, qryMarkGrid,
set the field to CDBL(testMark),
(Or some other numeric conversion )

Hi, I am not quite sure how to set that in the SQL?

 

[Access_Help]

Did you apply any format() to your calculated field to get your Percentage. If you did, it will be formatted as a string, not a number.

I have set it to general number but no other formatting has been applied

 

[Access_Help]

Do you have any external criteria in your source wry_mark_grid which is coming direct from a Form? Would be more helpful if you can share that database or both queries.

Here is the source query

 

[almahmood]

Either of your Test Marks or AvailableMarks has zero value which is evaluting #DIV/0! error. Edit your calculation like below:
IIF(NZ([Test Mark],0)=0,0,IIF(NZ([AvailableMarks],0)=0,0,[Test Mark]/[AvailableMarks]*100))

 

[Access_Help]

Either of your Test Marks or AvailableMarks has zero value which is evaluting #DIV/0! error. Edit your calculation like below:
IIF(NZ([Test Mark],0)=0,0,IIF(NZ([AvailableMarks],0)=0,0,[Test Mark]/[AvailableMarks]*100))

Hi. Thank you.

Despite the editing, I am still getting the mismatch error.

 

[Minty]

In a cross tab query you have to explicitly declare any criteria as parameters. From the screen shots you don't appear to have any criteria.

Right click and select the parameters option - is there something left behind in there you don't need?

 

[Access_Help]

Hi,

I have attached the database.

It has the source query as well as the crosstab query.

The query works fine if the mark field is used as the SUM, however, if I use the percentage field which is a calculated field, it brings up a mismatch error.

View attachment Cross_Tab_Display_%.zip

 

[JHB]

You've some bad data with the full name "Student100".
Correct that and the query runs.

 

[Access_Help]

You've some bad data with the full name "Student100".
Correct that and the query runs.

Hi, I changed the data to preserve confidentiality, it still did not run prior to this.

 

[JHB]

Exclude the student. The problem is that AvailableMarks is stored as text it should be number, and for courses no. 148 you have some spaces after AvailableMarks= 42.

 

[almahmood]

Hey,

You need to change the datatype for AvailableMarks in Courses table from Text to Number. Somehow you have junk data in it which is why it's leading into an datatype conversion error.

 

[Access_Help]

Thank you so much for spotting these minor mistakes.

 

[JHB]

Thank you so much for spotting these minor mistakes.

Whether it is minor or major error, depend on how you look at it.
It seems to have been a major one for you, because you couldn't run your query and you couldn't find the error even though I told you that you had some bad data.