List Box output on Report not formatted as expected

[Ginny2222]

Hi all,

I have a database which is used independently in a number of sites. Different versions of MSAccess are available in these sites. Here’s my problem.

The underlying tables have number fields formatted as double. There are queries which use operators to multiply and divide which result in any amount of decimal places. All queries are formatted using Standard and these look fine.

On the Reports the data is output in List Boxes. One List Box can contain the results of multiple queries. In all version of MS Access except version 2003 the results are keeping the Standard format. In MS Access 2003 the number that I have formatted as 123,456.78 appears as 123456.7765432.

Has anyone any solution to this??

Rgs
Ginny

 

[Ginny2222]

Additional Info: List Box output on Report not formatted as expected

Hi again,
It looks like I may be going about my database in the wrong manner, as I haven't received any replies!!

Could I be doing this in a better way? I have 5 unrelated (mostly) tables in my database. From these I produce data to be presented in variety of reports - Overall Summary, Summary by Area, All Areas by Category, Each Area by Category and Trends.

For each of my queries I can produce an Autoreport no problem. This produces the data in text boxes, which I can format no problem. But for each of my reports I have 9 queries to use. It is when I try to combine the different queries into 1 report that I get stuck. Autoreport won't work with the amount of queries I need. I have tried combo boxes, but Access errors. List boxes work, but I cannot format them in Access 2003. I have managed to get them looking ok in all other versions, but they don't align right!!

For the Autoreport to work I need to set up a relationship between the queries, but while these might work for some of the queries, I know that for 2 of them this will be difficult. e.g. For 2 queries I have a description and 4 fields - figures, and for 7 I have a description and 8 fields, a figure and percentage.

All help deeply appreciated.
Ginny

 

[DCrake]

You need to apply the format to column data in the list box, such as

Format(Amt,"#0,00.00")

David

 

[Ginny2222]

Hi David,

Which field in the properties of the List Box does this formatting go into. I can't find an appropriate field for it. I'm attaching a screenshot of the fields available.

rgs
Ginny

You need to apply the format to column data in the list box, such as

Format(Amt,"#0,00.00")

David

 

[DCrake]

In your underlying query that contains the column that needs formatting you need to put it there

David

 

[Ginny2222]

That means I have to change every query that I have built. These were already formatted using the Property option within the queries and set to standard. Isn't it most annoying that the Reporting area in access 2003 cannot cope with using this facility!!! All other versions of MSAccess can cope with it. I wonder what it is about 2003 that changed. Even Version 2007 is fine.

Thanks for the help David. I just hope this hasn't a knock-on effect anywhere else in my database!!

rgs
Ginny

 

[DCrake]

You should not be using list boxes in reports anyway. Listboxes are designed for forms not reports. This is not to say you cannot use them. Does the list box act correctly if on a form?

David

 

[Ginny2222]

Yes, David, I know I shouldn't be using list boxes on the reports. The amount of data that I am reporting on makes it too tedious to create text boxes for every value. Combo boxes will not work at all. I am going to investigate if I combine all 9 queries into 1, if that will allow me to create a crosstab query and then autocreate the report.

I have tried using a form to create all of the above and again MS Access 2003 is falling over. As I said before, this database is working perfectly in all other versions of Access except 2003. This is the mystifying thing. I have also tried different PC's in different locations and always the same.

rgs
Ginny

 

[DCrake]

Have you not tried using a sub report?

Create a simple report based on the listboxes underlying query and format the columns accordingly then paste this into the main report.

David