Link to home
Start Free TrialLog in
Avatar of joylene6
joylene6Flag for United States of America

asked on

ms access #TYPE! error on report when no data

Hello-

 I have an MS access report. Run perfect when where is data.   If there is NO data I get a #Type!  error on a particular field

In the  report itself, under the detail section I have a text box that contains a  TRIM statement:   =Trim([PRIMARY_NAME]) & " " & Trim([Label_NAME])

If there is no data on the report, I get #Type!  error showing on the report when I run the print preview.

I tried to modify the trim statement to be :   =IIf(IsNull([PRIMARY_NAME])," ",Trim([PRIMARY_NAME]) & " " & Trim([Label_NAME]))       but that did not work.

I wrote it so if the PRIMARY_NAME field was null then it would just output a blank space. This was probably wrong because there is 2 fields in my trim statement.


I am probably missing something simple....  can someone give advice how to get rid of that #Type! error?  Thank you.
Avatar of Nick67
Nick67
Flag of Canada image

IsNull vs Nz()
IsNull returns a Boolean, true or false
Nz(TheNullItem, TheSubstitute) substitutes something for the null.

Why are you TRIMing?
You should take care of that earlier.
Here's a fun trick.
In the query create two computed fields
tPRIMARY_NAME:Trim(PRIMARY_NAME)
tLabel_NAME: Trim(LABEL_NAME)

Make your control
=[tPRIMARY_NAME] + " " + [tLabel_NAME]

Only in Access controls does the + concatenation propogate null.
You'll only get the text of the Primary name, the text of the Label name, or both with a space in between.
Avatar of joylene6

ASKER

I was/am concatenating  the 2 fields.  I could put them together in the query.  
Just wondering if there was a way to do this in the report.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for explaining this to me. It did happen to work.