Solved

format number as percent in text when it is one already

Posted on 2012-03-14
6
419 Views
Last Modified: 2012-03-21
Hello Experts,

I am working on a report the has about 75 percent fields in it.  The data type in number (double) so using the existing "percent" format changes "50" to "5000.0%".  All I need it to do is added the percent symbol to make it "50%".  I'm working in textboxes in a report.  How can I do this using the format property?  

I've got to many fields to do an update query.  I could use controlsource = FieldName & "%", but that would require renaming all the text boxes as they have the same name as the field and that formula would cause a circular reference.

Please don't post the first thing the comes up from google.

Thank you
0
Comment
Question by:eshurak
  • 4
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37723336
First, ... 50 is not a percent, so Access is correct in making it 5000

If your "incorrect" Percents are 50 (meaning 50%), then you should really divide that number by 100

So make a query
SELECT Fld1, Fld2, [YourFakePercentField]/100 As RealPercent
FROM YourTable

Then format this new field as a percent.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37723343
like so...
Database71.mdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 400 total points
ID: 37723352
But to answer your question directly
Any time you have a Number (50) and you try to concatenate a text character to it (%), the result will always be a string.

<I could use controlsource = FieldName & "%", but that would require renaming all the text boxes as they have the same name as the field and that formula would cause a circular reference.>
Not sure I follow...?
First, your field name should rarely be the same as the control name.
For the reason you just illustrated, and others.
Use a standard VBA naming convention:
Field Name: FirstName
Control Name: txtFirstName

Double values can still be correctly formatted as percents...
The issue here seems to be that the numbers you are using are not true Percents..
Again, 50 cannot simply be converted to 50%
.5 is 50%

So perhaps you ultimate goal should be to go back to the source of this data and fix this issue there.

Let's see what other Experts may post...


JeffCoachman
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 100 total points
ID: 37723823
you mentioned you are working on a report... is it a report based directly on a table?

for a quick fix, i'd make a query from the table, and amend the query fields to be expressions of same name as field, and in the expression use:
fieldname: ([tablename]![fieldname]/100)&"%"

to fake it, and make your number into a string showing percent.
Test the query to make sure its showing how you want it, then set the query to show all the required fields..

Do this for one or two fields then go to sql mode, copy the sql string into notepad or excel, and use find and replace or columns in excel to create the same expression format for all 75 of the "percent" fields

then paste it back into the query sql editor and test the query.

If it works how you want it, save the query, then change the report record source to point to the new query, instead of the table... if you have all the field names correct, it should be seamless..

now check that the new % strings are showing correctly on the report.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37724778
Yeah,
You can always "Fake" the percentage....
Just prey that you never need to do calculations with this "Fake" percentage...
;-)

I realize that this is just a report, but that does not change the underlying issue...
The number is not a true percentage.

This is why I first suggested using a query and creating a "Real" percentage field and using this in the report.
With a real percentage value available, you can now do other calculations.
The query could also be used in other forms or Reports, thus eliminating the need to create fake calculations in each form/report.


JeffCoachman
0
 
LVL 3

Author Comment

by:eshurak
ID: 37748745
I went with doing an update query.  Thanks guys.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now