Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Data Type Mismatch in Criteria Expression 3464

dceglar1 (TechnicalUser) (OP)
2 Aug 05 10:22
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 (MIS)
2 Aug 05 10:27
The second IIf() is not complete.
GingerR (MIS)
2 Aug 05 10:33
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 (TechnicalUser) (OP)
2 Aug 05 10:49
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 (TechnicalUser) (OP)
2 Aug 05 14:42
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 (TechnicalUser) (OP)
2 Aug 05 15:02
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 (MIS)
2 Aug 05 16:12
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 (TechnicalUser) (OP)
2 Aug 05 16:30
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 (MIS)
2 Aug 05 16:36
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 (TechnicalUser) (OP)
2 Aug 05 16:48
Yes, thank you so much for your help!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close