Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Numeric/Text issue 2

Status
Not open for further replies.

dday01

Technical User
Feb 1, 2002
138
US
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
 
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
 
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
 
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
 
Hi Paul,

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

Thanks,

D
 
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
 
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
 
Now that is a solution and not a work around. Hope they see it.

Paul
 
John,

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

D
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top