• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

IIF results in #Type! in Access

I have an Access report that I want to grab 3 description fields and combine them using IIF so that if a field is null, it doesn't display anything.  I'm trying to use:

=IIf(IsNull([FIELD1]),"","FIELD1 DESC: " & [FIELD1]) & IIf(IsNull([FIELD2]),"","FIELD2 DESC: " & [FIELD2]) & IIf(IsNull([FIELD3]),"","FIELD3: " & [FIELD3])

But I get #Type! errors when I try to display the report.

I'm new to IIF but as I understand it, what I've done SHOULD work... shouldn't it?  Your assistance is appreciated.

I don't think it makes a difference, but to be clear, the data lives in a SQL database and Access is only the front end.
0
Lee W, MVP
Asked:
Lee W, MVP
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
Kelvin SparksCommented:
Have you tried this in a query first. If it still fails, have you tried each of the three parts on their own?

If all works, where in the report are you using this - that could be your issue.


Kelvin
0
 
MINDSUPERBCommented:
Hello Lee,

I've seen an issue with your IIF syntax. The IIF Syntax looks the same with the sample below:

=IIf([CountryRegion]="Italy", "Italian", IIf([CountryRegion]="France", "French", IIf([CountryRegion]="Germany", "German", "Some other language")))

You may try to follow the link below for the detailed explanation of the IIF.

http://office.microsoft.com/en-001/access-help/iif-function-HA001228853.aspx

Sincerely,

Ed
0
 
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
WEIRD thing.

(I'm working on a DB someone else created).

Anyway, I had that in what had been a field with ONE field in it - "FIELD1" so I TRIED to use the IIF and add three together (for space reasons, I needed to condense what was 3 separate boxes that rarely had even two used into 1).

Anyway, I went to the query that was pulling the data and tried to make a new field for it - it said I had an extra ) in it.  I looked CLOSELY - I DID NOT!  BUT, I decided to add one field at a time and it worked fine - cutting and pasting what I originally had back in one field and a time.  Go figure.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Kelvin SparksCommented:
I suspect some form of memory issue in Access. I've seen it before where I've had a syntax issue - corrected it, and it keeps repeating the original error. Often you need to delete it totally, develop something else, then rebuild the original.

Have never been able to figure it out, just build techniques for coping with it!

Kelvin
0
 
Kelvin SparksCommented:
Close request OK by me.


Kelvin
0
 
Dale FyeCommented:
Why not use a syntax like:

= ("Field1 Desc: " + [Field1]) & ("Field2 Desc: " + [Field2]) & ("Field3: " + [Field3])
0
 
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Because I don't want lines that read "Field1 Desc:" when there is no content in Field1.
0
 
Dale FyeCommented:
Lee,

if you notice, I used the "+" to concatenate each field to its description.  

If the field value is NULL, then concatenating it to its descriptive text using the "+" will result in the value inside of the ( ) being NULL.
0
 
PatHartmanCommented:
The reason that the statement didn't work in the report is because the columns were no longer in the report's RecordSource.  Why, you might ask?  The answer is - Because Access is smarter than we are and goes out if its way to help us.  In this case, it knows that the recordset used by the report can be a snapshot since it cannot be updated so it rewrites the select clause and only includes columns which are bound to a control on the report.  It also eliminates the Order By clause since so you MUST use the report's sorting/grouping options to get the report to sort correctly.  Luckily, forms are not so helpful and don't do this to you.

To fix the problem in the future, either create the field in the query (probably the best solution anyway) or bind the columns to controls.  You can make the controls very tiny and set their visible property to no.  I always make hidden controls a garish color that stands out like a sore thumb so it is obvious to me what they are.  You do need to be careful when laying out the report to prevent controls from overlapping.  Can Shrink/Can Grow will not work correctly if there are overlapping controls.  I bind the columns whenever I need to write code in the report's class module so the columns will be available in the code.
0
 
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
The steps I took resolved the issue but I appreciate the input and learned a bit from both posters.  Hence the split.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now