Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-11-2018, 06:37 AM   #1
MJ_Wilkinson
Newly Registered User
 
Join Date: Apr 2017
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
MJ_Wilkinson is on a distinguished road
Formatting as a Percentage on Report if Field is Text

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?

MJ_Wilkinson is offline   Reply With Quote
Old 01-11-2018, 06:45 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,558
Thanks: 10
Thanked 2,055 Times in 2,010 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Formatting as a Percentage on Report if Field is Text

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/.../isnumeric.php) on the report to see if you are working with a number or text, then format appropriately.
plog is offline   Reply With Quote
Old 01-11-2018, 06:59 AM   #3
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,503
Thanks: 72
Thanked 1,106 Times in 1,031 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Formatting as a Percentage on Report if Field is Text

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...02&postcount=6

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 01-11-2018, 09:02 AM   #4
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,737
Thanks: 52
Thanked 1,860 Times in 1,776 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Formatting as a Percentage on Report if Field is Text

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 01-11-2018, 02:22 PM   #5
MJ_Wilkinson
Newly Registered User
 
Join Date: Apr 2017
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
MJ_Wilkinson is on a distinguished road
Re: Formatting as a Percentage on Report if Field is Text

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

MJ_Wilkinson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Add Percentage sign to a text field without changing value l.kyriazakos Forms 3 04-08-2014 06:04 AM
Formatting address text field in Report agem Reports 1 11-12-2009 08:20 AM
Formatting in Text Field on Report in MS Access sgtSortor Reports 2 04-24-2009 01:34 PM
Text Field data conversion to Percentage Ginny2222 Queries 6 01-22-2008 11:06 AM
Converting a percentage field from a Text File cbochner Queries 5 12-31-2007 10:55 AM




All times are GMT -8. The time now is 07:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World