Report graph/chart format problem

Topic

New Reply

Help please on format of numbers in report chart/graph.
The chart is based on a query which takes data from a table.
The number field in the table is formatted as single to three decimal places.
The field in the query has its properties set to three decimal places.
The data table on the chart has its properties set to three decimal places
The datasheet for the chart is set to three decimal places
The data I have input myself as three decimal places e.g. 612.214
Yet when the chart is previewed/printed the data table shows the data as 612.2139919 or similar.
Other data whilst inputted as say 345.678 appears as 345.6781005
Where is this spurious data coming from and how do I enforce my formatting?
I know it’s only small values but this data for us is critical, these errors mean the difference between pass and fail QA systems for us. Help please
TIA
Alan
UK
Access 97
Windows NT sp5

Reply | Quote

Replies

go to the row source of the chart itself
set the format there
sounds silly but it worked for me
HTH
JerryC

Reply | Quote

Thanks Jerry but unfortunately the Row source for the chart is already containing the SELECT statement with SUM[Data] AS [Data points] etcetera. I have searched all help and knowledge base files but to no avail.
When I reached the row source to change the format I was back at my query which I have now changed to general format and 3 decimal places – still no luck with all numbers still showing 7 decimal places.
TIA
Alan

Reply | Quote

Hello Alan: I’ve had a lot of grief with graphs, I think what Jerry is suggesting is illustrated in the following, can you get to this point ?

Reply | Quote

Hello Brian,
Yes I have been to that stage and set the formats to fixed 3d.p.’s
I have also ‘rippled’ through all of the (known/common) stages at which the data is referenced to make sure it is set to 3d.p.’s
What is so puzzling and very annoying is that the figures have most definitely been inputted as say 345.220 and yet out of the ether 7d.p. figures are appearing of say 345.2199905 These data figures are totally fictitious yet are appearing to within .0000005 of the original figures (the actual variance is quite wide and not attributable to a calc but appears to be random)
TIA
Alan UK

Reply | Quote

in the row source you can set the format
something like this
Format([yourfield],”0.000″)
inside you select statement
hth
JerryC

Reply | Quote

JerryC,
Fantastic, saved my bacon.
Quite right you are that the format is to be changed in the row source.
fyi the original row source was along the lines of
SELECT [Data_Vehicle_Ref],Sum([Data])AS [Data point],
Sum([Nominal_Value]) AS [Nominal Value], FROM [qryChartData] GROUP BY [Data_Vehicle_Ref];

and has now been modified to:
SELECT [Data_Vehicle_Ref],Format(Sum([Data]),”0.000″) AS [Data point],Format(Sum([Nominal_Value]),”0.000″) AS [Nominal Value], FROM [qryChartData] GROUP BY [Data_Vehicle_Ref];

Still can’t understand where the last four dp’s came from, but now I don’t care.

Many many thanks.
All that remains now is to how to export the whole report and chart to word.
Regards
Alan
UK

Reply | Quote

You’re probably doing calculations that result in double or single values or your fields are doubles or singles. Floating point numbers are imprecise, and without using a function to round them yourself, they’re going to stay imprecise even if you display them differently. That’s one of the reasons to use the currency datatype for the field and suppress the currency display formatting elsewhere.

Reply | Quote

Thanks guys (metaphorically speaking),
I have now resolved the original problem but all of the replies have contributed to a better understanding of the underlying problem. I have also spent the last 18 months on vbc programming at collegiate and understand that the method by which the programme stores the data is a contributory factor in the resulting ‘loss’ of accuracy.
A simple example of this is in Excel, where if you perform a calculation which results in a known 2dp result, then copy the results into a new area as values only, you will quite often get a 2dp value with a string of 999999999999 at the end, hence rounding to your original result.
As I have found with a lot of MS applications, there are bugs and there are fixes, but quite often a fix is a workaround and hence not a fix. hmmmmm, when is a fix not a fix? It’s a programmers joke I’m sure 🙂
Thanks
Again

Reply | Quote

I tried the format method and got an error. Evidently, Access 97 SR2 only wants you to use format with dates.

Reply | Quote