Blank Fields

[Emma35]

Hi All,
I need a little assistance with a report i'm working on. If a user chooses to leave a field blank when entering information then that field will be blank on the resulting report. I'd prefer if the field displayed on the report displayed as N/A if left blank. I've tried the following code in the Control Source of the field on the report but it doesn't work (I'm getting a circular reference)

IIf([IncidentType] Is Null,"N/A",[IncidentType])

Ps...I've also tried that formula in the underlying query but it just comes up blank on the report.

Any help would be great,
Thanks,
Em

 

[pr2-eugin]

Try to do this in the On Format method of the Report.

 

[Emma35]

Thanks for the reply. Tried it but didn't work i'm afraid

 

[pr2-eugin]

Can you show the code you have?

 

[nanscombe]

IIf([IncidentType] Is Null,"N/A",[IncidentType])

I bet your ControlName is IncidentType as well?

It's probably a circular reference caused by trying to look at itself, try renaming the control.

 

[Emma35]

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
IIf([IncidentType] Is Null,"N/A",[IncidentType])
End Sub




The section IIf([IncidentType] Is Null,"N/A",[IncidentType])
is coming up in red and then i'm getting a syntax error message

 

[nanscombe]

Simply setting the Control Source of the control to =IIf([IncidentType] Is Null,"N/A",[IncidentType]) should work fine, as long as the control isn't (still) called IncidentType as well.

 

[Emma35]

Oh hang on....renaming the control did the trick. Looks like that's what was causing the circular reference. Thank you both for your help it's very much appreciated.

 

[nanscombe]

I'm glad that fixed it.

The control didn't know where the data was coming from, itself (IncidentType) or from the underlying data column (IncidentType).

 

[cbkebasen]

hello, i'm glad i've passed through here because this is exactly the problem I am facing right now. I also do want my report to show all fields even if they are blank. I did try the solutions posted above but the formula still shows circular reference.... any more possible solutions?

 

[oliber]

I'm glad that fixed it.

The control didn't know where the data was coming from, itself (IncidentType) or from the underlying data column (IncidentType).

I've just registered to ask the very same question, been looking for ages to find an answer.

iif(x=y, true, false) Its so annoying that the IIf always changes the value there is no ELSE leave the value alone!

 

[nanscombe]

hello, i'm glad i've passed through here because this is exactly the problem I am facing right now. I also do want my report to show all fields even if they are blank. I did try the solutions posted above but the formula still shows circular reference.... any more possible solutions?

A few clues would be useful:
What is the name of the control
What is the Control Source

I've just registered to ask the very same question, been looking for ages to find an answer.

iif(x=y, true, false) Its so annoying that the IIf always changes the value there is no ELSE leave the value alone!

It doesn't always change the result if the original data is in either the True or False part.

Let's take a look at the above example IIf([IncidentType] Is Null,"N/A",[IncidentType])

If IncidentType is Null (True) pass though "N/A"
If IncidentType is not Null (False) pass IncidentType through untouched.

The circular reference problem was because the control was also called IncidentType.

 

[oliber]

A few clues would be useful:
What is the name of the control
What is the Control Source




It doesn't always change the result if the original data is in either the True or False part.

Let's take a look at the above example IIf([IncidentType] Is Null,"N/A",[IncidentType])

If IncidentType is Null (True) pass though "N/A"
If IncidentType is not Null (False) pass IncidentType through untouched.

The circular reference problem was because the control was also called IncidentType.

I get what you mean and it makes sense.

if x=y then x="hi", if x<>y then x=x

I think its because I have experience in other languages.

So an if statement (pseudo code) looks like this:

If X=Y then X="hi" else endif

x=y, so value of x is changed to "hi".

x<>y then do not do anything to the value of x. ie x=x

The circular reference problem was exactly what you described, I added the table reference and [table1].[IncidentType] and it fixed that.

Thanks for the speedy reply and explanation!

 

[nanscombe]

I get what you mean and it makes sense.

if x=y then x="hi", if x<>y then x=x

I think its because I have experience in other languages.

So an if statement (pseudo code) looks like this:

If X=Y then X="hi" else endif

x=y, so value of x is changed to "hi".

x<>y then do not do anything to the value of x. ie x=x

Precisely.

The circular reference problem was exactly what you described, I added the table reference and [table1].[IncidentType] and it fixed that.

Thanks for the speedy reply and explanation!

You're welcome.

 

[cbkebasen]

A few clues would be useful:
What is the name of the control
What is the Control Source

I was able to figure this one out, thank you for your lead. However, my main problem still exists: The blank fields still don't appear on the print-out of the report. I've tried putting words if the source is blank and it does appear upon opening the report, but a different story when it is printed (the words don't show on the print out itself).

 

[nanscombe]

Maybe the fields are not Null but simply an Empty, or Zero Length, string?

To cope with Nulls and Zero Length strings you could adapt the code thus..

IIf(Len([IncidentType] & VbNullString) = 0,"N/A",[IncidentType])

But still the Control name needs to be different from the field name to avoid circular references.