Formatting the output of a query (Access 97 SR2)

Topic

New Reply

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 🙂

Reply | Quote

Replies

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

Reply | Quote

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 🙂

Reply | Quote

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.

Reply | Quote

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 🙂

Reply | Quote

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

Reply | Quote

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).

Reply | Quote

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….

Reply | Quote