## Data Type Mismatch in Criteria Expression 3464

## Data Type Mismatch in Criteria Expression 3464

(OP)

This works great:

=IIf([HasData],IIf([ESY MATH]="-1","Y") & IIf([ESY MATH]="",""))

This line just puts a Y if there is a -1 in the ESY MATH

field and nothing if there is not.

However, this does not work - I get the data type mismatch in criterial expression #3464:

=IIf([HasData],IIf([ESY LANG]="-1","Y") & IIf([ESY LANG]="",""))

I have experimented extensively - I only get the error when I include this field. And I only get the error when there is at least one -1 in the field. If there is nothing or a 0 in all the ESY LANG field, it works fine. I have even deleted this field in the underlying table and recreated it by copying the ESY MATH column and double checking that all properties are identical. I have also tried switching the type from text to a check box to no avail. This is happening in a report. I have a built in query which checks all fields which has criteria of Like "-1". This is working for the the other ESY fields (they are different Extended School Year subjects - (summer school)).

Any help would be greatly appreciated!.

=IIf([HasData],IIf([ESY MATH]="-1","Y") & IIf([ESY MATH]="",""))

This line just puts a Y if there is a -1 in the ESY MATH

field and nothing if there is not.

However, this does not work - I get the data type mismatch in criterial expression #3464:

=IIf([HasData],IIf([ESY LANG]="-1","Y") & IIf([ESY LANG]="",""))

I have experimented extensively - I only get the error when I include this field. And I only get the error when there is at least one -1 in the field. If there is nothing or a 0 in all the ESY LANG field, it works fine. I have even deleted this field in the underlying table and recreated it by copying the ESY MATH column and double checking that all properties are identical. I have also tried switching the type from text to a check box to no avail. This is happening in a report. I have a built in query which checks all fields which has criteria of Like "-1". This is working for the the other ESY fields (they are different Extended School Year subjects - (summer school)).

Any help would be greatly appreciated!.

## RE: Data Type Mismatch in Criteria Expression 3464

## RE: Data Type Mismatch in Criteria Expression 3464

What is in [HasData]? True/False?

iif([HasData] and [Easy Lang]="-1","Y","")

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at http://r937.com/relational.html

## RE: Data Type Mismatch in Criteria Expression 3464

Thank you for your help. I am not a good programmer although I have had some classes - I think its like art you can only learn so much then the "gift" needs to take over...

## RE: Data Type Mismatch in Criteria Expression 3464

=IIf([HasData],Count(IIf([ESY ENG] Or [ESY LANG]="-1","0")))

This way I only count the record once.

## RE: Data Type Mismatch in Criteria Expression 3464

=IIf([HasData],Count(IIf([ESY ENG] Or [ESY LANG]="-1","0")),"0")

## RE: Data Type Mismatch in Criteria Expression 3464

combining two sums? What sums?

Again, what is "HASDATA"? boolean? integer?

-1 = YES = TRUE. If you want, you can probably change the text field to a number field, save the table, then change it to a YES/NO field.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at http://r937.com/relational.html

## RE: Data Type Mismatch in Criteria Expression 3464

I was using a sum to get the count for each column in the footer:

=IIf([HasData],Sum([ESY READ])*-1,"0")

(it basically was adding all the -1s and then multipling by -1 to turn it into the positive {I couldn't get the abs thing to work}. However, this did not work in one instance where I only want one "Y" if a child has [ESY ENG] or [ESY LANG] - I am combining the outcome/check for a -1 in either of these fields into one summary expression. I switched the 'sum' to 'count' and used the following:

=IIf([HasData],Count(IIf([ESY ENG] Or [ESY LANG]

="-1","0")),"0")

IF there are records returned when the report is ran, count the records if the record has a "-1" in [ESY ENG] or [ESY LANG], if not put a "0". This is working great. I just wish it didn't take me so long to figure it out :(

## RE: Data Type Mismatch in Criteria Expression 3464

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at http://r937.com/relational.html

## RE: Data Type Mismatch in Criteria Expression 3464