Arrrgh Decimal Places (1 Viewer)

daveUK · Jun 22, 2003

Hi Guys

I have 2 tables, one, TblPaper, has Paper Size, Paper Quality, Paper Type and Price. The other, TblSale, has details such as date, staff name, unit price and total price and has a n-1 relationship with TblPaper.

I've created a form based on TblPaper and have a combo box on the form that has the all of the paper details in it. When the user 'clicks' on the combo box they can choose the type, size and quality of the paper and the price is also displayed. At the moment the price is only displayed as to 2 decimal places. I need it to display 4 decimal places. I've formatted the combo cox as General and set the Decimal Places to 4, but it still doesn't work.

Any ideas?

Dave

R. Hicks · Jun 22, 2003

You need to format the value in a field expression in the combo's Row Source query .... and show that value in the combo.

If you want all value to appear as 4 decimal places and show the trailing zero's ... use this:

myPrice: Format([Price],"$#,##0.0000")

If you want all value to appear as 4 decimal places and but only show the 4 decimal places when neded ... use this:

myPrice: Format([Price],"$#,##0.00##")

HTH
RDH

daveUK · Jun 22, 2003

Thanks for the reply, but no joy I'm afraid.

The row source of the combo box is currently

SELECT TblPaper.[ID No], TblPaper.[Paper Size], TblPaper.[Paper Quality], TblPaper.[Paper Type], TblPaper.[Sales Price] FROM TblPaper WHERE (((TblPaper.[Sales Price])>0)) ORDER BY TblPaper.[Paper Size] DESC , TblPaper.[Paper Quality];

If I run the query, the price is displayed to 4 decimal places, it's just when I use the combo box on the form that it only displays 2 and rounds the figures up

jaydwest · Jun 22, 2003

I've had similar problems and it really drives me crazy. What version of Access are you using? This problem seems to be worse in 2002.

I have had some success by formatting the field everywhere. So try to format it in the query too. If you're using the Query Design Window go to the column and select properties, and enter the format sent you by R Hicks. If you're generating the SQL in code, try this:

SELECT TblPaper.[ID No], TblPaper.[Paper Size], TblPaper.[Paper Quality], TblPaper.[Paper Type], Format$(TblPaper.[Sales Price] ,"$#,##0.00##") , "FROM TblPaper WHERE (((TblPaper.[Sales Price])>0)) ORDER BY TblPaper.[Paper Size] DESC , TblPaper.[Paper Quality];

It looks like you are in the UK so change the $ to Pound or Euro sign (whichever).

daveUK · Jun 22, 2003

Still no joy

I'm using Win XP and Access 2000. I've tried entering the info from yourself and R Hicks in the Query Design Window and by generating the code myself abd it still doesn't work.

I can't understand why it works when I run the query, but doesn't in the form.

R. Hicks · Jun 22, 2003

Executing the Row Source query will show the 4 decimal places .. but not the the combo display.

To get the information to display in the combo as you want .. you will need to create a "field expression" as I suggested in my first reply. This field expression must be created in the "Row Source" query of the combobox. The formatted result will appear in the format you want.

RDH

daveUK · Jun 22, 2003

I really appreciate your help Ricky, but unfortunately I'm going to need a bit more.

The row source of my combo box now has the SQL string

SELECT [TblPaper].[ID No], [TblPaper].[Paper Size], [TblPaper].[Paper Quality], [TblPaper].[Paper Type], [TblPaper].[Sales Price] Format£(TblPaper.[Sales Price] ,"£#,##0.00##") , "FROM TblPaper WHERE ((([TblPaper].[Sales Price])>0)) ORDER BY [TblPaper].[Paper Size] DESC , [TblPaper].[Paper Quality];

When I try to run the query, I get an error message

"Syntax error (missing operator) in expression '[TblPaper].[Sales Price] Format£(TblPaper.[Sales Price] ,"£#,##0.00##")'"

When I click on the combo box, it is now blank and has no record whatsoever.

Really sorry to annoy you, but as you've probably guessed I am pretty clueless when it comes to SQL

jaydwest · Jun 22, 2003

Ricky is correct. Try the following SQL

SELECT [TblPaper].[ID No], [TblPaper].[Paper Size], [TblPaper].[Paper Quality], [TblPaper].[Paper Type], Format(TblPaper.[Sales Price] ,"£#,##0.00##") AS fmtSalesPrice FROM TblPaper WHERE ((([TblPaper].[Sales Price])>0)) ORDER BY [TblPaper].[Paper Size] DESC , [TblPaper].[Paper Quality];

or

SELECT [TblPaper].[ID No], [TblPaper].[Paper Size], [TblPaper].[Paper Quality], [TblPaper].[Paper Type], Format(TblPaper.[Sales Price] ,"£#,##0.0000") AS fmtSalesPrice FROM TblPaper WHERE ((([TblPaper].[Sales Price])>0)) ORDER BY [TblPaper].[Paper Size] DESC , [TblPaper].[Paper Quality];

jaydwest · Jun 22, 2003

Correction. If you are entering SQL in code, replace double quotes in the Format Function with singe quotes

SELECT [TblPaper].[ID No], [TblPaper].[Paper Size], [TblPaper].[Paper Quality], [TblPaper].[Paper Type], Format(TblPaper.[Sales Price] ,'£#,##0.00##') AS fmtSalesPrice FROM TblPaper WHERE ((([TblPaper].[Sales Price])>0)) ORDER BY [TblPaper].[Paper Size] DESC , [TblPaper].[Paper Quality];

or

SELECT [TblPaper].[ID No], [TblPaper].[Paper Size], [TblPaper].[Paper Quality], [TblPaper].[Paper Type], Format(TblPaper.[Sales Price] ,'£#,##0.0000') AS fmtSalesPrice FROM TblPaper WHERE ((([TblPaper].[Sales Price])>0)) ORDER BY [TblPaper].[Paper Size] DESC , [TblPaper].[Paper Quality];

daveUK · Jun 23, 2003

Hooray!!!!

Thanks jaydwest and R. Hicks, it now works.

I can see that R Hicks was correct from the beginning, it's just my extreme lack of SQL knowledge

Thanks again

Dave

Arrrgh Decimal Places (1 Viewer)

daveUK

Registered User.

R. Hicks

AWF VIP

daveUK

Registered User.

jaydwest

JayW

daveUK

Registered User.

R. Hicks

AWF VIP

daveUK

Registered User.

jaydwest

JayW

jaydwest

JayW

daveUK

Registered User.

Similar threads

Users who are viewing this thread