Solved

format number as percent in text when it is one already

Posted on 2012-03-14
6
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

717 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