Numerical Formatting in a Report

Hello,

I am trying to whittle down some duplicate files on our server.  One of the
lines in the report provides a summary of a particular duplicated file.  In
the text box control, I have the following:

="There are " & [subCounter] & " additional copies of '" & [File_Name] & "'
potentially using up to " & [footsum] & " MB of space."

which works, but is ugly.  The [footsum] entity is calculated by dividing the
file size in bytes by 1024 and again by 1024 to yield MB.  Is there a way to
round the [footsum] calculation to 3 digits after the decimal, instead of the
14 or so that it wants to put there?  

I did set the properties to "Fixed" and 3 decimals, and that worked when the
only thing that the textbox was putting out was the contents of [footsum].

Are there any switches that can be incorporated into the "& [footsum] &"
portion of the statement to control the formatting of this value?

Thanks,

Dale

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200705/1

0
bridgfod
5/24/2007 3:52:08 PM
access.reports 4434 articles. 0 followers. Follow

9 Replies
894 Views

Similar Articles

[PageSpeed] 59

bridgfod,
   Try inserting this into the concatenation...
.... & Format([FootSum],"#.000") & ...
-- 
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

"bridgfod via AccessMonster.com" <u19296@uwe> wrote in message news:72a95b2c5bbb2@uwe...
> Hello,
>
> I am trying to whittle down some duplicate files on our server.  One of the
> lines in the report provides a summary of a particular duplicated file.  In
> the text box control, I have the following:
>
> ="There are " & [subCounter] & " additional copies of '" & [File_Name] & "'
> potentially using up to " & [footsum] & " MB of space."
>
> which works, but is ugly.  The [footsum] entity is calculated by dividing the
> file size in bytes by 1024 and again by 1024 to yield MB.  Is there a way to
> round the [footsum] calculation to 3 digits after the decimal, instead of the
> 14 or so that it wants to put there?
>
> I did set the properties to "Fixed" and 3 decimals, and that worked when the
> only thing that the textbox was putting out was the contents of [footsum].
>
> Are there any switches that can be incorporated into the "& [footsum] &"
> portion of the statement to control the formatting of this value?
>
> Thanks,
>
> Dale
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200705/1
> 


0
Al
5/24/2007 4:14:54 PM
Bob Barnes wrote:

>Similar question...no answer to yours.
>
>I have...
>=(Round([Text148]/([Text147]/60),2))
>
>And set the Report Field to Fixed, 2 decimal places...but....
>42.00 displays as 42
>53.60 displays as 53.6.


Try setting the text box's Format property to:

	0.00

-- 
Marsh
MVP [MS Access]
0
Marshall
5/24/2007 4:50:37 PM
Al,

Entering 0.00 converts by access to Fixed (from Marsh)...didn't work.
Tried the "#.000" below and it didn't work either.

Thank you - Bob


"Al Campagna" wrote:

> bridgfod,
>    Try inserting this into the concatenation...
> .... & Format([FootSum],"#.000") & ...
> -- 
> hth
> Al Campagna . Candia Computer Consulting . Candia, NH USA
> Microsoft Access MVP
> http://home.comcast.net/~cccsolutions
> 
> "Find a job that you love, and you'll never work a day in your life."
> 
> "bridgfod via AccessMonster.com" <u19296@uwe> wrote in message news:72a95b2c5bbb2@uwe...
> > Hello,
> >
> > I am trying to whittle down some duplicate files on our server.  One of the
> > lines in the report provides a summary of a particular duplicated file.  In
> > the text box control, I have the following:
> >
> > ="There are " & [subCounter] & " additional copies of '" & [File_Name] & "'
> > potentially using up to " & [footsum] & " MB of space."
> >
> > which works, but is ugly.  The [footsum] entity is calculated by dividing the
> > file size in bytes by 1024 and again by 1024 to yield MB.  Is there a way to
> > round the [footsum] calculation to 3 digits after the decimal, instead of the
> > 14 or so that it wants to put there?
> >
> > I did set the properties to "Fixed" and 3 decimals, and that worked when the
> > only thing that the textbox was putting out was the contents of [footsum].
> >
> > Are there any switches that can be incorporated into the "& [footsum] &"
> > portion of the statement to control the formatting of this value?
> >
> > Thanks,
> >
> > Dale
> >
> > -- 
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200705/1
> > 
> 
> 
> 
0
Utf
5/24/2007 5:52:01 PM
Fixed should also work in this case.

I can't explain why, but what you're describing is the
effect of using format General Number.  The only way I can
think of that happening is if your control source expression
is somehow being treated as a text value.
-- 
Marsh
MVP [MS Access]


Bob Barnes wrote:
>Entering 0.00 converts by access to Fixed...didn't work.
>Tried the "#.000" below and it didn't work either.
>
>> Bob Barnes wrote:
>> >Similar question...no answer to yours.
>> >
>> >I have...
>> >=(Round([Text148]/([Text147]/60),2))
>> >
>> >And set the Report Field to Fixed, 2 decimal places...but....
>> >42.00 displays as 42
>> >53.60 displays as 53.6.
>> 
>
>"Marshall Barton" wrote:
>> Try setting the text box's Format property to:
>> 
>> 	0.00
0
Marshall
5/24/2007 7:13:52 PM
Bob Barnes wrote:
>The Field on the Access Report is..
>=Format((Round([Text148]/([Text147]/60),2)),"Fixed")
>
>I've tried all kinds of "Format"....something like 62.00
>displays as 62, and 34.50 displays as 34.5.
>
>IF we could only get the "Format - Cells - Number - 2
>Decimal Places" like in Excel...
>
>Could this be a "No Go"??  The Post before mine...tried
>the same thing w/ 3 decimal places.


Well, that's not what I was suggesting, but it should have
worked.

What I was suggesting was to leave the control source
expression as:  =Round([Text148]/([Text147]/60),2)
and set the Format **property** to 0.00 or Fixed.

But, either approach should produce the same result, which
they did in my tests.

Maybe someone else has an idea, but I can't explain whatever
is causing your problem.

-- 
Marsh
MVP [MS Access]
0
Marshall
5/25/2007 12:23:01 AM
From Marsh...
> What I was suggesting was to leave the control source
> expression as:  =Round([Text148]/([Text147]/60),2)
> and set the Format **property** to 0.00 or Fixed.

Marsh - That's what I tried...It's Access 2003 under Windows 2000.
I wonder if there's "any chance" the most recent Security Updates
(about 2 weeks ago) has affected this???  A fellow worker has said
his PC Apps have been adversly affected since the Windows 2000
Security Update 2 weeks ago.

We're willing to settle for this...what's our alternative??

Thank you..Bob

"Marshall Barton" wrote:

> Bob Barnes wrote:
> >The Field on the Access Report is..
> >=Format((Round([Text148]/([Text147]/60),2)),"Fixed")
> >
> >I've tried all kinds of "Format"....something like 62.00
> >displays as 62, and 34.50 displays as 34.5.
> >
> >IF we could only get the "Format - Cells - Number - 2
> >Decimal Places" like in Excel...
> >
> >Could this be a "No Go"??  The Post before mine...tried
> >the same thing w/ 3 decimal places.
> 
> 
> Well, that's not what I was suggesting, but it should have
> worked.
> 
> What I was suggesting was to leave the control source
> expression as:  =Round([Text148]/([Text147]/60),2)
> and set the Format **property** to 0.00 or Fixed.
> 
> But, either approach should produce the same result, which
> they did in my tests.
> 
> Maybe someone else has an idea, but I can't explain whatever
> is causing your problem.
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
5/25/2007 12:41:01 AM
Bob Barnes wrote:

>From Marsh...
>> What I was suggesting was to leave the control source
>> expression as:  =Round([Text148]/([Text147]/60),2)
>> and set the Format **property** to 0.00 or Fixed.
>
>Marsh - That's what I tried...It's Access 2003 under Windows 2000.
>I wonder if there's "any chance" the most recent Security Updates
>(about 2 weeks ago) has affected this???  A fellow worker has said
>his PC Apps have been adversly affected since the Windows 2000
>Security Update 2 weeks ago.
>
>We're willing to settle for this...what's our alternative??


Since I have never used Win2000, I can not comment
intelligently on what, if any, problems exist in your
configuration.

Before going off on that kind of tangent, I would try
creating a new database with a simple form that tried to
reproduce the problem in the simplest situation you can
create.  If that works, then start adding in a few parts of
your problem db until the problem reappears and you can
isolate the part that's causing trouble.  If you can add
everything without getting the problem, then I guess you can
chalk it up to some kind of corruption.

-- 
Marsh
MVP [MS Access]
0
Marshall
5/25/2007 1:48:58 AM
Al,

Thank you very much.   That was exactly what I was looking for.  Have a good
weekend.

Dale

Al Campagna wrote:
>bridgfod,
>   Try inserting this into the concatenation...
>... & Format([FootSum],"#.000") & ...
>> Hello,
>>
>[quoted text clipped - 19 lines]
>>
>> Dale

-- 
Message posted via http://www.accessmonster.com

0
bridgfod
5/25/2007 1:45:16 PM
Me - not so smart...I hadn't set the Fixed 2 in the Detail Textbox...In 
Design View,
I had Fixed 2 ONLY on the Group Footer.

Thank all of you (in "General Questions" also)..

Bob


"Al Campagna" wrote:

> bridgfod,
>    Try inserting this into the concatenation...
> .... & Format([FootSum],"#.000") & ...
> -- 
> hth
> Al Campagna . Candia Computer Consulting . Candia, NH USA
> Microsoft Access MVP
> http://home.comcast.net/~cccsolutions
> 
> "Find a job that you love, and you'll never work a day in your life."
> 
> "bridgfod via AccessMonster.com" <u19296@uwe> wrote in message news:72a95b2c5bbb2@uwe...
> > Hello,
> >
> > I am trying to whittle down some duplicate files on our server.  One of the
> > lines in the report provides a summary of a particular duplicated file.  In
> > the text box control, I have the following:
> >
> > ="There are " & [subCounter] & " additional copies of '" & [File_Name] & "'
> > potentially using up to " & [footsum] & " MB of space."
> >
> > which works, but is ugly.  The [footsum] entity is calculated by dividing the
> > file size in bytes by 1024 and again by 1024 to yield MB.  Is there a way to
> > round the [footsum] calculation to 3 digits after the decimal, instead of the
> > 14 or so that it wants to put there?
> >
> > I did set the properties to "Fixed" and 3 decimals, and that worked when the
> > only thing that the textbox was putting out was the contents of [footsum].
> >
> > Are there any switches that can be incorporated into the "& [footsum] &"
> > portion of the statement to control the formatting of this value?
> >
> > Thanks,
> >
> > Dale
> >
> > -- 
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200705/1
> > 
> 
> 
> 
0
Utf
5/25/2007 3:29:00 PM
Reply:

Similar Artilces:

Fill down conditional formatting series
This surly is a simple question with a equally simple answer. B1:B10 require the following formula: =B1=True =B2=True =B3=True and so on when I format B1 and fill down the B1 is still referenced Conditional formatting automatically assigns the CF formula to all the cells within the seleciton. Select the cell/Range (say B1:B10). Please note that the cell reference B1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection If this post helps click Yes --------------- Jacob Skaria "gootr...

Conditional Formatting #44
How do you get text in cell A1 to appear in a bold font if a dat appears in cell B1? Can this be done in conditional formatting, if so, how -- Message posted from http://www.ExcelForum.com Hi! The following will recognise a _value_ in B1 and make A1 bol accordingly. Select A1 and click Format > Conditional Formatting. Choose Formula is (instead of Cell value is) and put in the bo =$B$1<>"" . This says B2 is not empty. Click Format... and choose Bold. OK. OK. A date is just a number with a particular format. If your need is t spot an entry (rather than a blank) in col ...

Numeric currency to words
I would like to use a user created function to read a cell containing a numeric currency value and put it into words, ie. $103.40 is One Hundred Dollars and Forty Cents. Any help? Thanks! Here is one way (you would want to use "Dollar" as the second argument to the function)... Go into the VB Editor (Alt+F11) and add a Module to the Workbook (Insert/Module from the VBA menu) and then paste in all of the code appearing after my signature into the Module's code window. You can call the function from your work sheet like this =NumberAsText(A1) where A1 is assumed ...

How to make staff sales report and stock report
Dear All First table with names. second table with store items. I read the northwind for preparing invoices which does not fit my requirement. Pls give me links for me to read up on how to prepare report on persons who have made purchases by linking these two tables. Pls give any links where I can read on how to go about to prepare a report on persons who made purchases; report of stores opening stock, quantity sold and stock balance etc. Edit/Delete Message On Wed, 19 May 2010 10:09:01 -0700, CAPTGNVR <CAPTGNVR@discussions.microsoft.com> wrote: >Dear All >...

Expected shipments report for drop ships only
I'm trying to run an expected PO shipments report for drop- ships. Report writer is able to accept the restriction for showing only Standard POs by entering the restiction PO Type = 1. But the report does return any records if you use PO Type = 2 or <> 1. The report shows Drop Ships only if you are in Report Writer, but not if you access the report in Great Plains. And yes, I've checked security. How do I print the expected shipments PO report from GP (not report writer) for Drop Shipment POs only? ...

dang cell format
I'm trying to set an Excel cell format like I can do in Access to prevent input errors. Cell input is 1234-123-1234-123, that's to say, non-sequential numbering but using 4 numbers dash, then 3 numbers dash, 4 numbers dash, then 3 numbers. If the entry is wrong, go to halt/stop. What hasn't worked; 1. conditional format 2. Format, cells, 'custom' I've tried ####-###-####-### under custom but it fails because it allows more then 4 numbers at the very start. I "can" get, 123456-789-1234-234 The last 3 sequences work but not the 1st seque...

pipe delimited format
how do i save a spreadsheet in pipe delimited format? i am using excel 2002, thanks Here's one way, works well if you only need to do it a couple of times! Save as a csv. change the extension of the csv to txt open the file, select all and copy paste it into word use replace to replace "," with "|" select all, copy and paste back into the text file If you have to do it numerous times your best bet is a macro, post back if that's what you need Dan E "rick" <elewis9125@aol.com> wrote in message news:059901c356d0$b29f5a80$a101280a@phx.gbl... >...

Update a Report from a Form
I have a button on a form that opens a report based upon that form. I want to be able to change the data in the record, then simply click the button to update the report. The button needs to update the record (without moving to the next record), close the last report if it is still opened, then open the new report (or simply update it) ...

Crystal Reports 9 timeout 10-14-04
Good afternoon! When creating a report using Crystal Reports Wizard in Crystal Reports 9, Crystal seems to just hang indefinitely. Any ideas? What version for Crystal reports you are using ?. You should be using Crystal Reports 9.0.22. Please follow the link regarding Crystal. http://www.microsoft.com/BusinessSolutions/Community/CRMCrystalReportsFAQ.aspx We are using 9.2.2 - the correct version. "Dignity" wrote: > What version for Crystal reports you are using ?. You should be using > Crystal Reports 9.0.22. Please follow the link regarding Crystal. > > >...

Cell color change without using conditional formatting
I have a multi-tab workbook with several checklists. Once item on list is complete owner puts YES in status box and cell turns green. I have a combined list of all the tabs on another sheet. I need the combined list to pull the YES response from each individual checklist tab and also turn Green. Conditional formatting will not allow cross worksheet actions. Is there another way to pull all the YES data into the overall checklist? thanks Trese Trese, Conditional Formating WILL allow cross worksheet actions, but the cell(s) referenced on the other worksheet must use an Excel Name (Inse...

help with header row format
Hi, The first row in my excel file is the header. When I insert a new row after that, the new row takes the format, color etc of the header. How can I avoid the new rows inserted form taking the format of the forst row? Thanks If it's Excel 2003, when you insert the new row under the header row youy should get a small icon appearing which has a drop down arrow providing format options. You can select to format the new row as the line above, the line below, or clear formatting. -- Noel O'K "Chris" wrote: > Hi, > The first row in my excel file is the header. W...

2007 Excel Table and Pivot Table Formatting
Hi, Just wondering why when I use the standard formatting (ie design ) designs in excel 2007 for tables and pivot tables, when I send this to someone that does not have excel 2007, they get this with no colour, no bold subtotals etc. Thanks ...

Database text converted to numeric
I Imported data from a SQL Database, it is numeric and alpha numeric but mostly numeric numbers (Serial numbers). I need to compare this list to another Excel list containing the same information but with some numbers missing. I pull this into Access and when I try and compare them the error is that the format has to be the same. I changed the format of the list I imported to Numeric (most of the numbers is only numbers) but now I have to press F2 and enter on every single number (database is only 227500 lines) to convert it to a Number. I thought it was because it still saw itself as a Dat...

Format Axis #2
I am doing a project ion school and i have to make a population pyramid. I have filled out all the data with males being negative. I have the chart Wizard up and on my school sheet it is asking me to dounble click on the verticel axis, Format Axis should appear but I can't seem to get it. Any help? If you are using Excel 2007, you will have to right-click and select 'Format Axis...' Double-clicking should work in older versions of the program. -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 double-c...

exporting reports to email
I am trying to export reports directly to email in html format. When I click export I get a 'Microsoft Office Outlook' box "The operation failed". Click OK and a 'Store Operation Manager' box appears with "Export Failed". Click OK and back to my active report. I can export to desktop and then email by attach ment but would rather do it in one step. Thanks for any suggestions. Michael On Oct 29, 9:13 am, astro <as...@discussions.microsoft.com> wrote: > I am trying to export reports directly to email in html format. When I click > export I ...

Random Conversion of alpha/numeric text with letter "e" to exponen
OS - w2003, office 2003, desktop XP Pro/ w2000 Pro My company uses a six digit alpha - numeric string for customer account numbers. a few are all numeric, most are in the format: ##X###, where # = numeric and X = an alpha character. A few accounts, 101 out of 360, that contain an "e" as the alpha character are converted to an exponential by Excel. the other 259 are not. These 101 accounts where the first accounts created using "e", the subsequent 259 accounts are not converted by Excel. Anyone have a clue as to what is going on and how I can stop it? Thanks, --...

A FREE new 12-page special report on Asian and Indian Stocks
Dear Friends, A FREE new 12-page complimentary special report on Asian and Indian stocks, has been released by Elliott Wave International. Investment Opportunities for Asia's Big 6 Markets will give you specific forecasts and valuable commentary and observations for the following markets: India's SENSEX Japan's Nikkei 225 Hong Kong's Hang Seng & MSCI China's Shanghai & Shenzen Singapore's Straights Times Australia's ASX 200 & All Ordinaries This FREE 12-Page Report was written by an analyst who lived in Asia for most of the 1990s and knows the ...

Conditional data point formatting with variable conditions?
Hi, I checked out Jon Peltier's great example/tutorial on conditional chart formatting. I would like to conditionally format data points of a scatter plot but the difficult thing about it is that the min-max conditions are variable. I have two data columns, x and y, yielding the scatter plot (Side note: In the thread "Diagonal line in scatter plot?" I described the data in a bit more detail.). The conditions for giving a data point a certain color are not supposed to be fixed values but instead the ratios of x and y. Let's say the ratio of x any y is above 1.5 or below 0.7, ...

Automated Report Queue
Has anyone managed to find a way to generate multiple reports from RMS on a scheduled, auto-run basis? There are several RMS reports I would like to run each night, but it's a pain to have to go into each report and run them individually. I would rather setup a report queue that runs every night at a set time. Any thoughts? Thanks, Paul Arenson Anderson Plywood Sales Culver City, CA Hi Paul, We have not found a way to do this...however if you are using Crystal Reports for analysis, there is a component called Crystal Enterprise that does scheduling and delivery. However......

Adding New Cell Formats
I have added a few custom Cell formats to the list given with Excel. #,##0 etc. I have adde 0) and (0) etc. Is there a formula I can write to enter feet and inches so I don't have to use ' & " everytime I enter. I have to type 1'-2". Is there a way I can format the cell so I just have to enter 1-2 or something short like that? thanks dpal ...

sorting alpha numerics
I have a list of documents that I need to sort by title. Example AFI10-229 AFI10-245 AFI10-1101 After I sort, the list looks like this AFI10-1101 AFI10-229 AFI10-245 How do I make it take the entire alphanumeric into account? Let A2:A4 house the sample to sort. In B2 enter & copy down: =--REPLACE(A2,1,SEARCH("-",A2),"") Sort A2:B4, with column A in ascending order and column B in descendin order. Sproove Wrote: > I have a list of documents that I need to sort by title. Example > > AFI10-229 > AFI10-245 > AFI10-1101 > > After I sort, th...

How do I format cells so that room numbers such as 1402, 1405a will sort correctly?
I'm trying to fix an Excel spreadsheet for a health care facility so that it can sort data by the first column, whci is the patient's room number. Room numbers can be pure numerical, or have a suffix at the end (a or b). So I have e.g. room numbers 1306, 2204, 1401b, 1613, 1402b. At the moment, the column sorts so that all the room numbers with a suffix follow all the numbers without a suffix. I've formatted the cells as General, Numbers, or Text, and get the same sort result each time. How can I fix this? HELP! All help gratefully received and acknowledged. -- Dan E...

Tax report not exporting to Excel
I have the "Tax related Transactions" report set for the following: Rows & Columns: Rows-Subcategories Columns - Payees Type - Summary View as a report All taxable accounts (IRA/401(k) accounts unchecked) All payees Relevant taxable categories Previous Year (1/1/2004-12/31/2004) When I click Export to Excel, I type in the file name. Money.exe goes to 100% and that's it. If I change the COLUMN option from Payees to Totals Only, it works. Otherwise I have to kill Money and reopen it. I have run the SuperSalvage. No joy. Office 2003 SR1, all fixes. Windows XP Sp2, all f...

text form to numeric ?
E.g A cell contain 1233- but the value is actually a numric value whereby the "-" behind is a negative sign, wat function do I use to convert it to a numeric form causing it to to become -1233 ? One way, assuming that the values are mixed (some with/without "-" sign) and that the values are in colA, which is formatted as text; IF(RIGHT(Ax,1)="-",LEFT(Ax,LEN(Ax)-1)*-1,VALUE(Ax)) -- Regards; Rob ------------------------------------------------------------------------ "crapit" <littlecramp@yahoo.com> wrote in message news:OSAgKf$1DHA.2324@TK2MS...

Conditional Formatting Question #6
I want the formattin of a series of cells to be dependent on the data i another cell. For instance, if A1 is a result of "something" then A1:A will all turn red. How do I get that done? -- livifivi ----------------------------------------------------------------------- livifivil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3691 View this thread: http://www.excelforum.com/showthread.php?threadid=56628 On Sat, 29 Jul 2006 12:31:02 -0400, livifivil <livifivil.2bptsg_1154190996.9165@excelforum-nospam.com> wrote: > >I want the formattin of...