Changing decimal places

[stupesek]

I am building a new database holding laboratory analysis information. The results of our analysis may have decimal places ranging from 0-4. I have a field in the table holding the analytical results to tell how many places right of the decimal to print. How do I use that integer to format the control printing the result on a form. I don't wish to have separate reports for differing decimal places. Thank you.

[weekend00]

in the query, format the numbers to text.

e.g.
table: tbl1
num1,num2,decimals
1.2345 2.1233 3
0.01 0.25 2

SELECT Format([num1],Left("0.0000",[decimals]+2)) AS n1
FROM tbl1;

you will get:
N1
1.234
0.01

[stupesek]

Thank you for your help,

This is what I added to the query, when I run it it returns "*****". What am I missing?

Format([AnalysisResultNumeric],Left("0.000",[DecimalPlaces]+2)) AS PrintResult

[weekend00]

is lysisResultNumeric a number field or text field?

[weekend00]

and check [DcimalPlaces] in you table. check if there any nulls. DecimalPlaces should a number field.

[stupesek]

There are nulls throughout that field, I just added [DecimalPlaces] to the table, but the query criteria is only returning records where I have entered 2 in the [DecimalPlaces] field of the record.

The [AnalysisResultNumeric] is numeric not text because I will have other reports that I will need to make calculations off of that [AnalysisResultNumeric] number.

[weekend00]

It works well in my test. Can you show me your full query?

[stupesek]

Full query:

SELECT [FirstName] & " " & [LastName] AS FullName, IIf(IsNull([LastName]),[CompanyName],[LastName] & ", " & [FirstName]) AS FileAs, Customer.*, Report.*, Sample.*, Analysis.*, [City] & ", " & [State] & " " & [ZipCode] AS FullCity, Report.ReportNumber, [CompanyName]+Chr(13) & Chr(10)+[FirstName]+" "+[LastName]+Chr(13) & Chr(10)+[Address] & Chr(13) & Chr(10) & [City] & ", " & [State] & " " & [ZipCode] AS FullAddress, Format([AnalysisResultNumeric],Left("#.####",[DecimalPlaces]+2)) AS PrintResult
FROM ((Customer RIGHT JOIN Report ON Customer.CustomerID = Report.CustomerID) RIGHT JOIN Sample ON Report.ReportID = Sample.ReportID) RIGHT JOIN Analysis ON Sample.SampleID = Analysis.SampleID
WHERE (((Report.ReportNumber)=[Enter Report Number:]));

Keep in mind I haven't graduated to SQL, I built the query in the Access design format.

[stupesek]

I apologize, it does work. I was looking at the wrong column... oops

But the resulting table does drop the trailing zeros, which I cannot do.

[stupesek]

Just fixed to trailing zero issue too changed "#" to "0".

[weekend00]

please use "0.0000" instead of "#.####".
Or #.0000 if you want.

and you may know how "#,###.0000" works, remember to change to DecimalPlaces]+6 if you use this.

[stupesek]

Thank you for your help once again. One final question. What is the +6 in the "DecimalPlaces]+6" portion of the equation?

[weekend00]

I meant if you use "#,###.0000" as format string, you need to :

Format([AnalysisResultNumeric],Left("#,###.0000",[DecimalPlaces]+6))