How to format numbers in Access Report using VBA

Question

Hello Everyone,

I have very limited knowledge of Access VBA. I have a report and its Record Source is a query. Screen short of my report is below. I want to format fields 2 to 9 (1990 - 1997) values based on Unit value. If in Units column value is "Times" or "Amount" or "Days"  then values of column 1990 to1997 should be formatted as "Standard". How if value in Units column is "Percent" then values column 1990-1997  should be formatted as "Percent". May I know if this is possible using VBA? What Event of Report I should use? Can someone please share some code to achieve this objective. Thanks in advance.

Answer 1

For each of the 'year' controls use an expression as its ControlSource, e.g.

=IIf([Unit]="Percent",Format([1990],"Percent"),Format([1990],"Standard"))

Answer 2

You have to format the data at the query level. The reason is that in a continues form, if you set a text box format (say using VBA), then the format will apply to ALL ROWS.

There is only “one” instance of a text box, and it is automatic repeated over and over – so any change to the properties of that text box for one row will apply to all rows.

You have to remove the formatting from the text box (it will have to be plain Jane text box without formatting).

You then have to format the data in the query that drives the report.

The expressions will be rather messy. You might consider building a function VBA function called MyFormat(fType, fColumValue).

Say:

Public Function MyFormat(fType As Variant, fValue As Variant) As String

Select Case fType

Case "Times"

MyFormat = Format(fValue, "HH:NN")

Case "percent"

MyFormat = Format(fValue, "percent")

Case "what ever"

End Select

End Function

Then in your query, you go:

Select a, b, Units, etc., etc., MyFormat([Units], [1990]) as f1990,

MyFormat([Units],[1991]) as f1991 etc. etc. etc.

So you can dynamic format with the above approach.

Another approach would be to send the data to a temp table, and then process the data with VBA and format. You then launch the report on that temp table.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 3

Thank you so much Mr. Ken.  The solution you suggested worked very fine and all the numbers were formatted as desired. I used the following expression in txtbox1

=IIf([Forms]![frmUserInput]![cboNumberOfYears]>=1,(IIf([Unit]="Times",Format([Year1],"#,##0.00; (#,##0.00)"),IIf([Unit]="Amount",Format([Year1],"#,##0.00;(#,##0.00)"),IIf([Unit]="Days",Format([Year1],"#,##0"),IIf([Unit]="Percent",Format([Year1],"Percent")))))),"")

For rest of nine textboxes, I have used the above expression by changing value of number of years (2,3,4,5 .....).

With the above expression I have another problem in my report. I am sure with your help I can solve it.

My report is based on a dynamic query that takes all the data from a table. This table is again a dynamic table that will calculate values based on the number of years selected by the user in User Input form. A user can select maximum 10 years.

I have 10 textboxes in my report, one for each year. Now with the help of above expression what I am trying to achieve is that if User selects 5 years in the form, above expression should set ControlSource As Nothing. I have tried following versions of expression but could not get desired results:

=IIf([Forms]![frmUserInput]![cboNumberOfYears]>=10,(IIf([Unit]="Times",Format([Year10],"#,##0.00; (#,##0.00)"),IIf([Unit]="Amount",Format([Year10],"#,##0.00;(#,##0.00)"),IIf([Unit]="Days",Format([Year10],"#,##0"),IIf([Unit]="Percent",Format([Year10],"Percent")))))),Nothing)

=IIf([Forms]![frmUserInput]![cboNumberOfYears]>=10,(IIf([Unit]="Times",Format([Year10],"#,##0.00; (#,##0.00)"),IIf([Unit]="Amount",Format([Year10],"#,##0.00;(#,##0.00)"),IIf([Unit]="Days",Format([Year10],"#,##0"),IIf([Unit]="Percent",Format([Year10],"Percent")))))),IsNull)

=IIf([Forms]![frmUserInput]![cboNumberOfYears]>=10,(IIf([Unit]="Times",Format([Year10],"#,##0.00; (#,##0.00)"),IIf([Unit]="Amount",Format([Year10],"#,##0.00;(#,##0.00)"),IIf([Unit]="Days",Format([Year10],"#,##0"),IIf([Unit]="Percent",Format([Year10],"Percent")))))),False)

Is there any way that I can set ControlSource of textbox as blank using above expression.

Thanks.

Answer 4

Just use "" (two consecutive quote marks) as the final argument.