Numeric/Text issue 2

[dday01]

Hi All,

I have got a problem I can't resolve. I have a report that has four fields, two of them are numeric. There are null values in a couple of these numeric fields. I would like to have the report display "NO DATA" in these fields that are null, however any formulas I try I get a data mismatch error. Is there any way around this? I need to keep the fields numeric because I do calculations on them. And with the null values that are currently there, there are several "Blank" spots in the report. Any help would be greatly appreciated.

Thanks

D

 

[PaulBricker]

One way to handle this is in the underlying query.
In a new column in your query put this
MyNewField:IIf(IsNull(NumericFieldName),"No Data",NumericFieldName)

This will give you a field that is set up the way you want. Then reference this field in your Report.


Paul

 

[dday01]

Thanks Paul, That should work. There is no way "NO DATA" can be displayed in the numeric field? I kind of wanted to avoid adding a new field to the report. Your Help is much appreciated.

Thanks,

D

 

[PaulBricker]

None that I know of. The other way would be to put a second control directly under your Numeric control that has a control source set to ="No Data". Set it's Visible Property to No. Then in the Format event for the section you would put this code.

If IsNull(NumericFieldName) Then
Me.TextControlName.Visible = True
Me.NumericControlName.Visible = False
Else
Me.TextControlName.Visible = False
Me.NumericControlName.Visible = True
End If

This will toggle the two controls so the appropriate one is visible.

Paul

 

[dday01]

Hi Paul,

That sounds pretty good, I am going to give that a try. Much appreciated.

Thanks,

D

 

[PaulBricker]

When you run the Report, the text field will be justified left and the numeric field justified right. You can change this by setting the Text Align property for your text field to Right, and lining up the right edges of the two textboxes.

Paul

 

[JohnLowell]

You can use a defined format in the format property of the text box.

The definition can be broken down into 4 types: positive, negative, zero and Nulls

This is a example of a format that will cover all types of numbers for the text box:

#,###0;(#,###0);0;"NO DATA"

I Hope This Helps

 

[PaulBricker]

Now that is a solution and not a work around. Hope they see it.

Paul

 

[dday01]

John,

I did see it, that works great! thank you both for your suggestions!

D