Solved

format number as percent in text when it is one already

Posted on 2012-03-14
6
422 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…

821 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