Data Type Mismatch in Criteria Expression 3464

[dceglar1]

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

 

[lupins46]

The second IIf() is not complete.

 

[GingerR]

I don't understand your code--why the third IIF statement?

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

 

[dceglar1]

The simplified expression that GingerR gave me works great. I have already switched it for all the ESY*** fields. But I still get the same error when working with the ESY LANG field. There are several ESY*** fields and I only have a problem with the ESY LANG field - and only when it contains at least one "-1".

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

 

[dceglar1]

I was able to figure it out - I was somehow combining two sums incorrectly - so I used this:

=IIf([HasData],Count(IIf([ESY ENG] Or [ESY LANG]="-1","0")))
This way I only count the record once.

 

[dceglar1]

correction - the above code did not properly give a "0" if there were no records that met the criteria.

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

 

[GingerR]

still not working?

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

 

[dceglar1]

HasData only performs the expression if there are records returned when you run the report. I don't know where HasData originates from - I saw it in Access samples on the web. I created a report that has the students names, id #, campus etc. and then has a "Y" under the appropriate columns for several ESY courses (extended school year - summer school). Then in the footer, I have a sum/count of the number of kids in each ESY course, i.e, [ESY READ], [ESY MATH]....etc. I also have a built-in query to return only the records that have a "-1" in any of the ESY fields.

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

 

[GingerR]

so is it all working now?

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

 

[dceglar1]

Yes, thank you so much for your help!