Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250

    Unanswered: trouble formatting numbers in report/chart's table

    Type Size(Bytes) Count
    1. Doc 3504734702 15236
    2. MDB 221444096 89
    3. PDF 1318642421 1466
    4. PPT 2263787785 2568
    5. TIF 638916054 1312
    6. XLS 871322123 3956
    7. SUB TOTAL 8818847181 24627
    8. ALL OTHER TYPES 0 0
    9. Server Total 8818847181 24627
    Ref. Avail. Space 0 0


    A 10 (union) select statement creates the above table. It produces a report after combining same type of chart in a query joined by "type". The chart is shown in the zipped bmp below. My difficulty is making the format for the size and count to be better managed/displayed.
    1) I'd like to at least get commas into the format.
    2) I'd also like to get a more normalized number-- I'd like to display Megabytes instead of bytes.
    On the first I did it before, but don't know why it's difficult this time. I get one field to format as standard, but no decimal places option. Also, the successful attempt had both fields formatted as standard/0 decimal places. FYI the sql for the joined query which feeds the chart(s):

    SELECT qryFileList4cmpRpt.Type, qryFileList4cmpRpt.Count AS [FileList Ct], qryArchiveList4cmpRpt.Count AS [ArchiveList Count]
    FROM qryArchiveList4cmpRpt INNER JOIN qryFileList4cmpRpt ON qryArchiveList4cmpRpt.Type = qryFileList4cmpRpt.Type;

    On the second, I did divide by 1 Meg, but now it seems to miss the table name. Could the later problem be something with the sql having same field names-- the table names differ though? When I try to add the table name to the math (/1Meg) it can not find the table (or field).

    Ideas any body?
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Change the query so the data looks the way you want it to look. If it is for a report, then it should not matter if the numbers are actually strings. Use the Format command to add the commas and divide by 1024 to get kB.

  3. #3
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    All,
    Thanks, I was ablel to spend a bit of time to figure things out. I had to use the following command(s):
    Cint(Sum(FileList.FileSize)/1000000)
    inside each select statement in the union query; works like a charm.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •