Avatar of joylene6
joylene6
Flag 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.
Microsoft AccessVisual Basic ClassicSQL

Avatar of undefined
Last Comment
joylene6

8/22/2022 - Mon
Nick67

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.
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
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
joylene6

ASKER
Thank you for explaining this to me. It did happen to work.
Your help has saved me hundreds of hours of internet surfing.
fblack61