Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting the output of a query (Access 97 SR2)

    Hey all,

    Has anyone tried to format the output of a query in code?

    Okay, here's what I'm trying to do. If I build a query (in this case, it's a crosstab), I can set the properties to make the output show the format Standard, fixed with 2 decimal places. But since I want my crosstab to have parameters that the user chooses from a form, I end up building a querydef and running it. What I'd like to do is also set the format for the numbers fields so that they have two decimal places.

    Any ideas?

    Cecilia :-)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formatting the output of a query (Access 97 SR2)

    Queries are simply a way of looking at data. Why are you trying to format them from code? A report would be easier.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting the output of a query (Access 97 SR2)

    Normally, I'd agree. But in this case the user is going to use the data, probably cut & paste a bit, even put it in Excel. And there are several queries (all crosstabs that may have different numbers of columns based on the options that the user picks) that I'm creating on the fly, which is why I didn't go with forms (datasheets) or reports (which don't work well for what I'm doing anyway).

    It really isn't horribly important, since he's probably going to export to excel anyway, he can format it there, but my job is to make his life easier, so I wanted to have it all formatted. If it can't be done, that's fine ;-) If you have any suggestions for displaying crosstabs that are created on the fly, I'd love to hear! :-)

    Thanks!

    Cecilia :-)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formatting the output of a query (Access 97 SR2)

    If you know the datatype for the value, you can use the format function in the value expression to force the format. Otherwise, you're just taking what you get. There are ways to display crosstabs in a report without know in advance exactly what you're going to get, but they rely heavily on code and you do at least have to know the maximum number of columns you'll get.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting the output of a query (Access 97 SR2)

    Thanks for the advice, Charlotte :-)

    I'm not afraid of writing code, but bottom line is that I'll never know how many columns I need. I supposed I could just populate a datasheet with the maximum number of columns and in code have them display or not display, but I think the code there would not be worth all the effort expended.

    Format won't work, because it makes my numeric fields into strings. I need them to stay numbered, and to have them aligned as numbers. I was hoping I could somehow modify the format property of the query (or qdf in this case), but I can't seem to find anything.

    Ah, well. Back to thinking up more things that are just impossible ;-)

    Cecilia :-)

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Formatting the output of a query (Access 97 SR2)

    If you are still interested in pursuing this, I've attached sample procedure that will create a crosstab query where number of columns is not known in advance, and where the numerical values are in "Standard" format with two decimal places.

    In attached [PIVOT_FLD] is the field you "pivot" on (ie, the field that will supply the column headings). Using same "where" criteria as the crosstab will use, you open a recordset using SELECT DISTINCT to figure out what the column headings will be, then assign these to a dynamic array. Also use this recordset to construct an "IN" clause for the crosstab. The rst recordcount is used to redimension the array.

    As for formatting, in a crosstab query each column heading is a field so you have to set properties for each. The Format and DecimalPlaces properties are not found in the field properties collection till you set these properties explicitly (normally in query design view) but if setting in code you have to append these properties first. I never understood the reason behind this... For test purposes procedure opens query so you can see results. After you do whatever you are doing with results you can always delete query. When exported results to Excel using generic OutputTo method the values were exported as numbers and were formatted with 2 decimal places.

    This may be a little convoluted, but maybe you can adapt it for your needs.
    HTH
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting the output of a query (Access 97 SR2)

    Hi Mark,

    Very nice! Your code works fine, but I have one small quibble:

    You have to change the type of the DecimalPlaces property to dbByte instead of dbInteger:

    Set prop = qry.CreateProperty("DecimalPlaces", dbByte, 2)

    Since 2 is the default number of decimal places, you won't see a difference. But if you try another number, the property doesn't work if it's dbInteger (in my Access 97, at least).

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Formatting the output of a query (Access 97 SR2)

    Thanx, you're correct, didn't catch that! Only tested using 2 decimal places. Property not listed in Object Browser, I "assumed" it was an integer data type...

    Note regarding previous post: In cases where you want to display ALL possible column headings, whether or not the crosstab returns value for that column, omit the WHERE criteria from the SELECT DISTINCT SQL statement used to open recordset that populates array with column heading values. Also, SELECT DISTINCT by default will list unique records in ascending order, you can specify different sort order by including ORDER BY clause. If you want "custom" xtab column order (not ascending or descending) then I think it'd have to be "hard-coded" in the IN clause....

Posting Permissions

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