Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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