?
Solved

format number as percent in text when it is one already

Posted on 2012-03-14
6
Medium Priority
?
433 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 1600 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 400 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

588 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