format number as percent in text when it is one already

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
LVL 3
eshurakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
like so...
Database71.mdb
0
Jeffrey CoachmanMIS LiasonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Simon BallChief information OfficerCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
eshurakAuthor Commented:
I went with doing an update query.  Thanks guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.