Formating the output of report's TextBox

[John S. Ford, MD]

I am creating a report that uses a query as a record source. A series of
TextBoxes on the report are bound to some of the query's fields that are
themselves calculated percentages. These fields i.e. Percent1, Percent2,
etc. use IIf conditional statements such that they return a proportion if
the denominator of the calculation is nonzero. It returns "NA" (Not
Applicable) if the denominator is zero thus avoiding a divide-by-zero
problem.

The query works perfectly returning either the proportion or a "NA". The
problem is, I want the report's TextBoxes to format these numbers as
percentages with one decimal point. I have the TextBoxes' Format property
set to "Percent" with the Decimal Point Property set to 1.

When I print the report, it seems to ignore the TextBox Format and Decimal
Point properties and prints the values as simple proportions with endless
decimal points. Any idea why my TextBoxes are behaving this way? The data
presented by the TextBoxes is correct just not formatted the way I want.
Does this have something to do with the fact that one of the possible
outcomes of the query is the string "NA" and not a number?

Thanks in advance!

John

 

[Geoff]

John,

There are probably a number of different approaches you could adopt. Here's
one solution that produces what you're after. As the explanation is worse
than the doing of it, here is a step-by-step procedure.

The problem stems from the fact that you are mixing text data ("NA") with
numeric data.

1. Change the query so that it returns only numeric data or Null (meaning
no valid data). Do this by replacing the expression in the query to
something like:

CalcField: IIf([Data2]=0,Null,Round([Data1]/[Data2],3))

The Round() function is used to round the result to three decimal places
after the decimal point. This will give one decimal place after the number
is converted to the Percent format.

2. Still in query design view, right-click the CalcField and select
Properties from the right-click menu. In the Properties dialog, on the
General Tab, click in the Format property and select "Percent". In the
Decimal Places property, select 1. Save the query and run it. For valid
data , you should see percentages rounded to 1 decimal place and, for
invalid data, you should see blanks.

3. Open the Report in design view. The current Textboxes now display the
information incorrectly, but you need to leave them on the report so that
Access can get to the data in those fields in the Report's recordsource.
Make these Textboxes invisible (by changing their Visible property to No).
Create new unbound Textboxes for each calculated field and give each Textbox
a suitable name; eg for the [CalcField] field above, create a Textbox named
"txtCalcField". Set these unbound Textboxes to Percent format and one
decimal place.

4. Double-click the gray bar across the top of the Detail Section to open
the Properties dialog for the Detail Section. Click the Event tab and click
in the Format Property. In the Format Property, select [Event Procedure].
To the right of the Format property, click its Build button (the square
tablet with the elipsis ...). The Visual Basic editor opens and you are
taken to the Format event procedure for the Detail Section of the report.
This format event procedure runs for each record in the data that's being
printed. It's here where you have complete control over the formatting of
the data. In the following example, [CalcField] is the name of the field
calculated by the query. "Me" simply refers to the report. "txtTextBox" is
the name of the unbound Textbox created to receive the data. Your query now
returns Null for invalid data, so we can use the IsNull() function to test
for invalid data.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

' Do first calculated field:
If IsNull([CalcField]) Then
Me.txtTextBox = "N/A"
Else
Me.txtTextBox = [CalcField]
End If

' Repeat similar coding for every calculated field.

End Sub

5. After you've written the code, you can switch back to Access (eg by
using the Access button on the Taskbar at the bottom of your screen). Run
the report to see if it's working OK. The button for the VBA editor will
still be on the Taskbar if you need to switch back to the VBA editor to
adjust the code. Alternatively, you can repeat the above steps to get to
the Format event procedure for the Detail Section. When finished coding,
close the VBA editor.

If "N/A" wasn't so important, ie if blanks would be acceptable, you needn't
go to the trouble of writing code. However, if "N/A" is important, then
this is one way of getting Access to do it, while displaying the numeric
data exactly as you want it.

I hope this makes sense.
Geoff

 

[John S. Ford, MD]

Geoff,

Thanks for the detailed response. I think I understand it but when I tried
to use the Round() function as described in my query's SQL statement, I got
an error message:

"Undefined function 'Round' in expression."

Was this function only available after Access 97 (which is what I'm using)?

John

John,

There are probably a number of different approaches you could adopt. Here's
one solution that produces what you're after. As the explanation is worse
than the doing of it, here is a step-by-step procedure.

The problem stems from the fact that you are mixing text data ("NA") with
numeric data.

1. Change the query so that it returns only numeric data or Null (meaning
no valid data). Do this by replacing the expression in the query to
something like:

CalcField: IIf([Data2]=0,Null,Round([Data1]/[Data2],3))

The Round() function is used to round the result to three decimal places
after the decimal point. This will give one decimal place after the number
is converted to the Percent format.

2. Still in query design view, right-click the CalcField and select
Properties from the right-click menu. In the Properties dialog, on the
General Tab, click in the Format property and select "Percent". In the
Decimal Places property, select 1. Save the query and run it. For valid
data , you should see percentages rounded to 1 decimal place and, for
invalid data, you should see blanks.

3. Open the Report in design view. The current Textboxes now display the
information incorrectly, but you need to leave them on the report so that
Access can get to the data in those fields in the Report's recordsource.
Make these Textboxes invisible (by changing their Visible property to No).
Create new unbound Textboxes for each calculated field and give each Textbox
a suitable name; eg for the [CalcField] field above, create a Textbox named
"txtCalcField". Set these unbound Textboxes to Percent format and one
decimal place.

4. Double-click the gray bar across the top of the Detail Section to open
the Properties dialog for the Detail Section. Click the Event tab and click
in the Format Property. In the Format Property, select [Event Procedure].
To the right of the Format property, click its Build button (the square
tablet with the elipsis ...). The Visual Basic editor opens and you are
taken to the Format event procedure for the Detail Section of the report.
This format event procedure runs for each record in the data that's being
printed. It's here where you have complete control over the formatting of
the data. In the following example, [CalcField] is the name of the field
calculated by the query. "Me" simply refers to the report. "txtTextBox" is
the name of the unbound Textbox created to receive the data. Your query now
returns Null for invalid data, so we can use the IsNull() function to test
for invalid data.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

' Do first calculated field:
If IsNull([CalcField]) Then
Me.txtTextBox = "N/A"
Else
Me.txtTextBox = [CalcField]
End If

' Repeat similar coding for every calculated field.

End Sub

5. After you've written the code, you can switch back to Access (eg by
using the Access button on the Taskbar at the bottom of your screen). Run
the report to see if it's working OK. The button for the VBA editor will
still be on the Taskbar if you need to switch back to the VBA editor to
adjust the code. Alternatively, you can repeat the above steps to get to
the Format event procedure for the Detail Section. When finished coding,
close the VBA editor.

If "N/A" wasn't so important, ie if blanks would be acceptable, you needn't
go to the trouble of writing code. However, if "N/A" is important, then
this is one way of getting Access to do it, while displaying the numeric
data exactly as you want it.

I hope this makes sense.
Geoff

I am creating a report that uses a query as a record source. A series of
TextBoxes on the report are bound to some of the query's fields that are
themselves calculated percentages. These fields i.e. Percent1, Percent2,
etc. use IIf conditional statements such that they return a proportion if
the denominator of the calculation is nonzero. It returns "NA" (Not
Applicable) if the denominator is zero thus avoiding a divide-by-zero
problem.

The query works perfectly returning either the proportion or a "NA". The
problem is, I want the report's TextBoxes to format these numbers as
percentages with one decimal point. I have the TextBoxes' Format property
set to "Percent" with the Decimal Point Property set to 1.

When I print the report, it seems to ignore the TextBox Format and Decimal
Point properties and prints the values as simple proportions with endless
decimal points. Any idea why my TextBoxes are behaving this way? The
data
presented by the TextBoxes is correct just not formatted the way I want.
Does this have something to do with the fact that one of the possible
outcomes of the query is the string "NA" and not a number?

Thanks in advance!

John

 

[Douglas J. Steele]

Round didn't exist in Access 97: I believe it was introduced in Access 2000.

Take a look at http://www.mvps.org/access/modules/mdl0054.htm at "The Access
Web" for how to write your own Round routine.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Geoff,

Thanks for the detailed response. I think I understand it but when I
tried
to use the Round() function as described in my query's SQL statement, I
got
an error message:

"Undefined function 'Round' in expression."

Was this function only available after Access 97 (which is what I'm
using)?

John

John,

There are probably a number of different approaches you could adopt. Here's
one solution that produces what you're after. As the explanation is worse
than the doing of it, here is a step-by-step procedure.

The problem stems from the fact that you are mixing text data ("NA") with
numeric data.

1. Change the query so that it returns only numeric data or Null (meaning
no valid data). Do this by replacing the expression in the query to
something like:

CalcField: IIf([Data2]=0,Null,Round([Data1]/[Data2],3))

The Round() function is used to round the result to three decimal places
after the decimal point. This will give one decimal place after the number
is converted to the Percent format.

2. Still in query design view, right-click the CalcField and select
Properties from the right-click menu. In the Properties dialog, on the
General Tab, click in the Format property and select "Percent". In the
Decimal Places property, select 1. Save the query and run it. For valid
data , you should see percentages rounded to 1 decimal place and, for
invalid data, you should see blanks.

3. Open the Report in design view. The current Textboxes now display the
information incorrectly, but you need to leave them on the report so that
Access can get to the data in those fields in the Report's recordsource.
Make these Textboxes invisible (by changing their Visible property to
No).
Create new unbound Textboxes for each calculated field and give each Textbox
a suitable name; eg for the [CalcField] field above, create a Textbox named
"txtCalcField". Set these unbound Textboxes to Percent format and one
decimal place.

4. Double-click the gray bar across the top of the Detail Section to open
the Properties dialog for the Detail Section. Click the Event tab and click
in the Format Property. In the Format Property, select [Event
Procedure].
To the right of the Format property, click its Build button (the square
tablet with the elipsis ...). The Visual Basic editor opens and you are
taken to the Format event procedure for the Detail Section of the report.
This format event procedure runs for each record in the data that's being
printed. It's here where you have complete control over the formatting
of
the data. In the following example, [CalcField] is the name of the field
calculated by the query. "Me" simply refers to the report. "txtTextBox" is
the name of the unbound Textbox created to receive the data. Your query now
returns Null for invalid data, so we can use the IsNull() function to
test
for invalid data.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

' Do first calculated field:
If IsNull([CalcField]) Then
Me.txtTextBox = "N/A"
Else
Me.txtTextBox = [CalcField]
End If

' Repeat similar coding for every calculated field.

End Sub

5. After you've written the code, you can switch back to Access (eg by
using the Access button on the Taskbar at the bottom of your screen).
Run
the report to see if it's working OK. The button for the VBA editor will
still be on the Taskbar if you need to switch back to the VBA editor to
adjust the code. Alternatively, you can repeat the above steps to get to
the Format event procedure for the Detail Section. When finished coding,
close the VBA editor.

If "N/A" wasn't so important, ie if blanks would be acceptable, you needn't
go to the trouble of writing code. However, if "N/A" is important, then
this is one way of getting Access to do it, while displaying the numeric
data exactly as you want it.

I hope this makes sense.
Geoff

I am creating a report that uses a query as a record source. A series
of
TextBoxes on the report are bound to some of the query's fields that
are
themselves calculated percentages. These fields i.e. Percent1, Percent2,
etc. use IIf conditional statements such that they return a proportion if
the denominator of the calculation is nonzero. It returns "NA" (Not
Applicable) if the denominator is zero thus avoiding a divide-by-zero
problem.

The query works perfectly returning either the proportion or a "NA". The
problem is, I want the report's TextBoxes to format these numbers as
percentages with one decimal point. I have the TextBoxes' Format property
set to "Percent" with the Decimal Point Property set to 1.

When I print the report, it seems to ignore the TextBox Format and Decimal
Point properties and prints the values as simple proportions with endless
decimal points. Any idea why my TextBoxes are behaving this way? The
data
presented by the TextBoxes is correct just not formatted the way I
want.
Does this have something to do with the fact that one of the possible
outcomes of the query is the string "NA" and not a number?

Thanks in advance!

John

 

[John S. Ford, MD]

Actually, I found that I could use your procedure and have it work perfectly
WITHOUT using Round(). It seems that forcing my query to produce only a
number or a NULL enabled me to use the format properties of the query to
output a Percent.

I did however have to still use your trick with the TextBoxes and VBA in the
format event in the report to get the report's TextBoxes to output "N/A" in
the event of a divide-by-zero situation.

I'm delighted to have a workable solution but for the future, is there
really no less unwieldy solution for handling this?

Thanks for the great help!

John

Geoff,

Thanks for the detailed response. I think I understand it but when I tried
to use the Round() function as described in my query's SQL statement, I got
an error message:

"Undefined function 'Round' in expression."

Was this function only available after Access 97 (which is what I'm using)?

John

John,

There are probably a number of different approaches you could adopt. Here's
one solution that produces what you're after. As the explanation is worse
than the doing of it, here is a step-by-step procedure.

The problem stems from the fact that you are mixing text data ("NA") with
numeric data.

1. Change the query so that it returns only numeric data or Null (meaning
no valid data). Do this by replacing the expression in the query to
something like:

CalcField: IIf([Data2]=0,Null,Round([Data1]/[Data2],3))

The Round() function is used to round the result to three decimal places
after the decimal point. This will give one decimal place after the number
is converted to the Percent format.

2. Still in query design view, right-click the CalcField and select
Properties from the right-click menu. In the Properties dialog, on the
General Tab, click in the Format property and select "Percent". In the
Decimal Places property, select 1. Save the query and run it. For valid
data , you should see percentages rounded to 1 decimal place and, for
invalid data, you should see blanks.

3. Open the Report in design view. The current Textboxes now display the
information incorrectly, but you need to leave them on the report so that
Access can get to the data in those fields in the Report's recordsource.
Make these Textboxes invisible (by changing their Visible property to No).
Create new unbound Textboxes for each calculated field and give each Textbox
a suitable name; eg for the [CalcField] field above, create a Textbox named
"txtCalcField". Set these unbound Textboxes to Percent format and one
decimal place.

4. Double-click the gray bar across the top of the Detail Section to open
the Properties dialog for the Detail Section. Click the Event tab and click
in the Format Property. In the Format Property, select [Event Procedure].
To the right of the Format property, click its Build button (the square
tablet with the elipsis ...). The Visual Basic editor opens and you are
taken to the Format event procedure for the Detail Section of the report.
This format event procedure runs for each record in the data that's being
printed. It's here where you have complete control over the formatting of
the data. In the following example, [CalcField] is the name of the field
calculated by the query. "Me" simply refers to the report. "txtTextBox" is
the name of the unbound Textbox created to receive the data. Your query now
returns Null for invalid data, so we can use the IsNull() function to test
for invalid data.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

' Do first calculated field:
If IsNull([CalcField]) Then
Me.txtTextBox = "N/A"
Else
Me.txtTextBox = [CalcField]
End If

' Repeat similar coding for every calculated field.

End Sub

5. After you've written the code, you can switch back to Access (eg by
using the Access button on the Taskbar at the bottom of your screen). Run
the report to see if it's working OK. The button for the VBA editor will
still be on the Taskbar if you need to switch back to the VBA editor to
adjust the code. Alternatively, you can repeat the above steps to get to
the Format event procedure for the Detail Section. When finished coding,
close the VBA editor.

If "N/A" wasn't so important, ie if blanks would be acceptable, you needn't
go to the trouble of writing code. However, if "N/A" is important, then
this is one way of getting Access to do it, while displaying the numeric
data exactly as you want it.

I hope this makes sense.
Geoff

I am creating a report that uses a query as a record source. A series of
TextBoxes on the report are bound to some of the query's fields that are
themselves calculated percentages. These fields i.e. Percent1, Percent2,
etc. use IIf conditional statements such that they return a proportion if
the denominator of the calculation is nonzero. It returns "NA" (Not
Applicable) if the denominator is zero thus avoiding a divide-by-zero
problem.

The query works perfectly returning either the proportion or a "NA". The
problem is, I want the report's TextBoxes to format these numbers as
percentages with one decimal point. I have the TextBoxes' Format property
set to "Percent" with the Decimal Point Property set to 1.

When I print the report, it seems to ignore the TextBox Format and Decimal
Point properties and prints the values as simple proportions with endless
decimal points. Any idea why my TextBoxes are behaving this way? The
data
presented by the TextBoxes is correct just not formatted the way I want.
Does this have something to do with the fact that one of the possible
outcomes of the query is the string "NA" and not a number?

Thanks in advance!

John

 

[Geoff]

John:

Actually, I found that I could use your procedure and have it work
perfectly
WITHOUT using Round().

Oh sure - yes, of course, it would work just fine without the Round()
function. The Round() function wasn't crucial to the solution. And on
reflection, I'm not sure it was necessary anyway. I was thinking about
numbers like 0.1255 that might need to be shown as !2.6%, not 12.5%.
Perhaps they do anyway. I didn't test that.

I'm delighted to have a workable solution but for the future, is there
really no less unwieldy solution for handling this?

How did I know you were going to ask that!

It does, indeed, seem unwieldy. I think the functionality you wanted to
overcome is built into Access itself, which is making the best guess
possible for the majority of situations. Quite often, solutions rely on a
multi-stage approach to get the result you want, which can seem less than
elegant. I can't think of another way just now.

Glad you've got something to work with.
Geoff

 

[Geoff]

John:

In case you're interested:

If you need to perform a complex calculation in query, then you can,
instead, write a Visual Basic for Applications function and call the
function from the query - this is usually easier to deal with.

For example, say you find yourself nesting several Immediate-If (IIf)
statements in a query. You could write a function instead.

In the following example, the calculated field in the query, CalcField1,
gets its value by calling the VBA function (which you write)
"GetCalcField1". The query passes the fields [DataField1], [DataField2], and
[DataField3] to the function:

CalcField1: GetCalcField1([DataField1],[[DataField2],[DataField3])

You write a public function in a standard module that will receive the three
fields and return an appropriate value. Here's an example of a function that
returns a Variant to the query:

Public Function GetCalcField1(vntDataFld1 As Variant, _
vntDataFld2 As Variant, vntDataFld3 As Variant) _
As Variant

Dim vntRetVal as Variant

if vntDataFld1 = SomeValue then
if vntDataFld2 = SomeOtherValue then
vntRetVal = Value1ToSendBackToQuery
Else
vntRetVal = Value2ToSendBackToQuery
End If
Else
vntRetVal = Value3ToSendBackToQuery
End If

' Set this Function's return value using the Function's name:
GetCalcField1 = vntRetVal

End Function

As the Variant data type tends to be the slowest (because it accepts almost
any other data type), you might want to make declarations using specific
data types (eg As Long, As Integer, As String, etc).

Geoff