How to fix a #Type! error

[bearcub]

I have a report that is based upon a query where I'm getting the dreaded #Type ! error. All the query in the report shows up as it should but when I go to the report (which is comprised of 3 fields in the detail) I get this error in two of them.

In the header row, I have State Council Positions (like Minority at Large, Director at Large, etc). The people and their work offices are populated in the detail area below this

In the detail area, I include the name of the State Council member and their Alternate (if there is one). The query has a State Council Alternate field which either has True or False in each record. so the following two formulas are dependent upon the State Council Alternate field. In my query, the appear on the right side of the field, doe this mean that they are numeric (e.g. binary, not TRUE or False text)?

When I run each item field separate without the IIF statement, I get the expected results for each member. When I put the formula back together I get the #Type ! error.

This is the first formula in the first field (the first column with the #Type ! error.

=IIf([State Council Alternate]=False,[Individual],"        Alternate:")

This is saying that is a State council is not an "Alternate" (the State Council Alternate field in the source query is false) then add the members first and last name (the [Individual] field) in the formula into the report. Otherwise, if the State Council Alternate field (in the query) is true include " Alternate:" in the row instead.

So we would have something that looks like this:

Joe Blow (his State Council Alternate field in his record is false)
Alternate: (This is for the member whose State Council Alternate field is true).

The second formula I have is the following:

=IIf([State Council Alternate]=False,IIf([Local Name]=[State Council Positions],Null,[Local Name]),[Individual])

This one is saying if the State Council Alternate Field is False then enter the Individual's last name. Otherwise, include the office that they work out of the [Local Name] field.

So, Joe Blow and the Alternate would look like this:

Joe Blow          San Mateo Educators Association
     Alternate:   Sherry Stevens

The "IIf([Local Name]=[State Council Positions],Null," applies to situations where the position name is same as the local name. We have some instances where a member will represent an office and the state council position would be one of the same. if this is the case, no need to enter anything here.

I do have this DB linked to an Excel file which I think might be causing the issue.

In the original table (before I linked it to Excel), the State Council Alternate field was a check box. In my Excel file, it is shown as either True or False.

Could this True or False in my spreadsheet be coming over into Access as a numeric field instead of a text field, thus creating the #Type !?

This is the only thing that I could think about that could cause an error. when I built the report initially from a local table (with check boxes being used) it worked fine. Now, I'm having issues with it and I don't know where to start to debug it.

Thank you in advance for your help,

Michael

 

[xenou]

Could this True or False in my spreadsheet be coming over into Access as a numeric field instead of a text field, thus creating the #Type !?

Yes, this is possible. I'm not saying its clearly so, but from your description it seems you are not sure about what the actual data types are for your true/false values and so the first thing to do is to find out for sure what these datatypes really are.

I personally never link to Excel as it is a never ending source of trouble to treat a spreadsheet like a database. I would always prefer to import to Access and work with table data.

However, you can pretty easily tell if Excel is numeric or text (not 100% though since each cell can be different and that is just one of the problems with a spreadsheet!) -- if you type true in an Excel cell and it displays as TRUE, then it is numeric. If you type true and it stays as true, then it is text. However, it is still possible that Access would treat the data as text, so its still not guaranteed (and this is just one more of the problems you can face with Excel as a datasource ... you have to be extremely carefully and have good control over how the spreadsheet is being used to make sure your data is consistent).

 

[bearcub]

I think I found the problem. The TRUE FALSE State Council Alternate field is coming over from Excel as Boolean. However, the linked table for this field is set up as Text. I'm going to change the True False to Yes No and hopefully this should work. I'll have to modify my formula to do the same.

Thank you for your help,

Michael

 

[bearcub]

I just put quotation marks around the False in the IIF statement converting it from Boolean to Text. Report runs fine now. Just that minor tweak.

Thank you again for the tips.

Michael

 

[xenou]

Okay that makes sense.

 

[bearcub]

I'm hoping that my Excel to Access process works. The admins understand Excel better than Access so I was trying to make it easier for them. They only would use this for a short time during the year (around Oct/Nov). If they were using it all the time I think I would house the data mostly in Access and do it that way. The raw data is coming from so many different sources I thought it would easier to use Excel.

Hopefully, nothing will break,

Michael