Formatting as a Percentage on Report if Field is Text

[MJ_Wilkinson]

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]

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.

 

[isladogs]

Agree with plog

Having said that, you might be interested in this example of a complex method of conditional formatting on a report that I've just posted:
https://www.access-programmers.co.uk/forums/showpost.php?p=1561802&postcount=6

 

[arnelgp]

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.

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]

Thanks all for coming back to me on this, worked when I put in arnel's solution.