UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> #type! Error - How Do I Fix This?, Access 2016    
 
   
bearcublandon
post Feb 27 2018, 06:53 PM
Post#1



Posts: 12
Joined: 5-December 17



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.
CODE
=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:

CODE
=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:

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

I'm including 4 attachments that I think might help clarify what I'm describing to you. I know it is difficult to figure this out without seeing the actual problem

Thank you in advance for your help,

Michael
Attached File(s)
Attached File  What_it_should_look_like.PNG ( 219.72K )Number of downloads: 2
Attached File  Original_State_Council_Query.PNG ( 56.05K )Number of downloads: 2
Attached File  State_Council_Source_and_Access_Report.PNG ( 415.09K )Number of downloads: 2
Attached File  What_it_should_look_like.PNG ( 219.72K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Feb 27 2018, 08:11 PM
Post#2


UtterAccess VIP
Posts: 8,806
Joined: 25-October 10
From: Gulf South USA


Hi:

QUOTE
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!?

Could be. Without having your db to test, we can't know for sure. But you might try a test query that simply asks for the value of [State Council Alternate] and see what appears. And if it is a number, see if it is aligned left (text) or right (numeric). If it actually says "False", perhaps the value is text and needs quotes in you IIf. Then adjust your IIf statement accordingly.

At least, that's where I would start, if I understand your situation correctly ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
bearcublandon
post Feb 27 2018, 08:40 PM
Post#3



Posts: 12
Joined: 5-December 17



I did test the alternate field but itself and it did return True/False.

How would NZ be included in the IIF statement? Could this be like the IF error statement in Excel?

Thank you for your help.

Michael
Go to the top of the page
 
RJD
post Feb 27 2018, 08:49 PM
Post#4


UtterAccess VIP
Posts: 8,806
Joined: 25-October 10
From: Gulf South USA


Okay. Is it returning True or False as a text value? If so then the IIf should be ...

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

Should be worth a try.

Also, If the value might be Null (not a ZLF) the NZ can be used. NZ([State Council Alternate],"False") if you want a Null to return a text value of "False".

Or something like ... =IIf([State Council Alternate]="False" OR IsNull([State Council Alternative]),[Individual]," Alternate:")

Failing that, could you post a db (no sensitive data, relevant objects, zipped - 2MB max size)? That way we could avoid just guessing.

HTH
Joe

Added comment: You should also consider taking the spaces out of object names. Better practice.

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 02:41 AM