Formatting as a Percentage on Report if Field is Text (1 Viewer)

MJ_Wilkinson

Registered User.
Local time
Today, 20:52
Joined
Apr 26, 2017
Messages
15
Hi all,

I have a field on my report that contains a mixture of decimals (e.g 0.2544, 0.4567 etc) and text statements such as "No Leads" or "No Eligible Sales".

I'm trying to get the decimals to show as a percentage to one decimal place on my Access report, but keep the text statements as they are (which I want to then conditional format a different colour if the statement applies).

I've tried =Format([Q1M1],"0.0%") in the Control Source field but for some reason this is not working. The field is a Text field in the table, and if I try changing this it warns me I'll be deleting a lot of data that is not compatible.

Can somebody please put me out my misery and tell me what I'm doing wrong?
 

plog

Banishment Pending
Local time
Today, 15:52
Joined
May 11, 2011
Messages
11,611
You really shouldn't have a column that could be text or not. I suggest the correct solution is to generate a query that puts the text in one field and the numbers in another. Then on the report you would use an If statement to show the appropriate data.

Of course as a hack you can you can use the IsNumeric function (https://www.techonthenet.com/access/functions/advanced/isnumeric.php) on the report to see if you are working with a number or text, then format appropriately.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:52
Joined
May 7, 2009
Messages
19,169
add code to the Detail section's
Format Event. note that this can only
be used in Print Preview.
change "textToFormat" with the name of the
control you want to format.
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If IsNumeric(Me.textboxToFormat) Then
        Me.textToFormat.Format = "0.0%"
    Else
        Me.textToFormat.Format = "@"
    End If
End Sub
 

MJ_Wilkinson

Registered User.
Local time
Today, 20:52
Joined
Apr 26, 2017
Messages
15
Thanks all for coming back to me on this, worked when I put in arnel's solution.
 

Users who are viewing this thread

Top Bottom