CODEWITHSUNDEEP
vbams-access
TBoulz
TBoulz

Reputation: 351

Formatting Report Textbox After Returning Value

this is a follow-up post to my original post at TextBox Control Source with Nested IIF Statement is Returning Same Column Regardless

Lee Mac was able to help me get the appropriate syntax to return 1 of 3 potential values based on the value of another textbox. Now I am struggling to get the returned value in the appropriate format.

The values can be Currency or Percent, and they would be in the same textbox, so I've been trying an Event procedure to format the value, but no change is occurring.

My latest attempt, which results in the value always being #.####:

Private Sub Detail_Format()

If Me.Allowance_Type = "% Off Invoice" Then
    Me.txtbox4.Value = Format(Me.txtbox4.Value, "0.00%")
End If
If Me.Allowance_Type = "Per Pound" Then
    Me.txtbox4.Value = Format(Me.txtbox4.Value, "$###,###.##")
End If
If Me.Allowance_Type = "Per Case" Then
    Me.txtbox4.Value = Format(Me.txtbox4.Value, "$###,###.##")
End If

End Sub

I have also tried various other Events like Report_Load etc, and I've tried changing the line: If Me.Allowance_Type = to If Me.Allowance_Type.Value = but that results in #Name?

Is it possible to have 1 text box that can be conditionally formatted as Currency or Percent? Thanks in advance!

Upvotes: 0

Views: 737

Answers (3)

June7
June7

Reputation: 21370

Could do an expression in ControlSource and eliminate VBA.

=Format([fieldname], IIf([Allowance_Type] LIKE "Per*", "Currency", "Percent"))

Or include this formatting in the procedure that returns the value.

=Format(Switch([Allowance Type]="% Off Invoice",[Freight Paid % OI], ([Allowance Type]="Per Pound",[Freight Paid / LB], [Allowance Type]="Per Case",[Freight Paid/_Case]), IIf([Allowance_Type] LIKE "Per*", "Currency", "Percent"))

Format events only trigger in PrintPreview or direct to printer, not Report View.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16015

Per my comment in your other question, if you allow yourself to convert the data to a string, as opposed to retaining the original data type (and hence original precision), then you can use the Format function directly in your Switch expression, e.g.:

=Switch
(
    [Allowance Type] = "% Off Invoice", Format([Freight Paid % OI],  "0.00%"),
    [Allowance Type] = "Per Pound",     Format([Freight Paid / LB],  "$###,###.##"), 
    [Allowance Type] = "Per Case",      Format([Freight Paid/_Case], "$###,###.##"),
    True, ""
)

Otherwise, I would suggest using an event handler to change the .Format property of the textbox, triggered on the AfterUpdate event of the form control bound to your [Allowance Type] field.

Upvotes: 0

kismert
kismert

Reputation: 1692

Try using the .Format property, as in:

If Me.Allowance_Type = "Per Case" Then
    Me.txtbox4.Format = "$###,###.##"
End If

Upvotes: 0

Related Questions