Problem with decimals in a combobox in an ACCESS 2002-XP form

[Geo]

In an ACCESS 2002 XP form, i use a combo box to select a value from a query
and add it to a table.
The field in the query is like
Sum(lenght)
with the Format: Standard
Decimal places: 3
The field in the table has also 3 decimals (number with field size DOUBLE,
format STANDARD and Decimal Places 3).
The PROBLEM is that in the combo box I don't see anymore the number with 3
decimals. It is rounded at 2 decimals, so the number added in the table will
not be correct.If I set the format Standard and Decimal Places 3 for the
combo, that is just adding a "0" at the end of the ROUNDED number.
Can somebody,please, help me!
I need this number with 3 decimals to be correct because repesents a maximum
limit for a quantity.

 

[Allen Browne]

Try setting the Format to Fixed instead of Standard.

You may need to do that in the properties of the combo, as well as the field
in the table.

You are aware that the Double type cannot show exactly 3 places? It may be
better to use a field of type Currency, which can. You can still format it
Fixed, 3 places, so it does not look like currency.

 

[Geo]

I tried all you sugested, but it doesn't work!

Try setting the Format to Fixed instead of Standard.

You may need to do that in the properties of the combo, as well as the field
in the table.

You are aware that the Double type cannot show exactly 3 places? It may be
better to use a field of type Currency, which can. You can still format it
Fixed, 3 places, so it does not look like currency.

 

[Allen Browne]

Okay, let's start from the top. You have a field in a table that is:
Name Length
Type Number
Size Double

In a query, you have a field that uses:
Sum(Length)
It does not matter what properties you set in the query.

In a form, you have a combo box that has these properties:
Control Source SumOfLength
Format Fixed
Decimal Places 3
Limit to List ???
However, the combo does not display the 3rd decimal places.
Presumably the bound column is visible.

I'm not quite sure why a combo is appropriate here. There is only a fixed
number of possible choices to choose from? So the RowSource of the combo
supplies the possible choices?

Suggestions.
1. Open the table in design view, and change the field's Type to Currency.
This will prevent the rounding errors inherent in the floating point types.

2. Explicitly typecast the query field, i.e. use:
CCur(Nz(Sum([Length]),0))
Explanation of why:
http://members.iinet.net.au/~allenbrowne/ser-45.html

That should take care of the rounding errors, and the possibility for
misunderstanding the data type. The combo should then be able to follow your
instructions for formatting the data.

 

[Geo]

I use a combo because the data for it must respect some conditions ( is
like:
SELECT DISTINCT stoc2.Lenght FROM stoc2 WHERE
(((stoc2.Dimensions)=[cboDimensions]) AND ((stoc2.Name)=[cboName]) AND
((stoc2.Quality)=[cboQuality]));
where stoc 2 is the query where the data is taken from, the other combo
boxes are from the curent form :[cboName] has the row surce SELECT DISTINCT
stoc2.Name FROM stoc2;
[cboDimensions] : SELECT DISTINCT stoc2.Dimensions FROM stoc2 WHERE
(((stoc2.Name)=[cboName]));

cboQuality: SELECT DISTINCT stoc2.Quality FROM stoc2 WHERE
(((stoc2.Dimensions)=[cboDimensions]) AND ((stoc2.Name)=[cboName]));

I tried your advice (using CCur(Nz(Sum([Length]),0)) ), but in the combo
box it still doesn't appear the right value(the number is still rounded at 2
decimals even in the query has 3).
It seems I'm stucked.

Okay, let's start from the top. You have a field in a table that is:
Name Length
Type Number
Size Double

In a query, you have a field that uses:
Sum(Length)
It does not matter what properties you set in the query.

In a form, you have a combo box that has these properties:
Control Source SumOfLength
Format Fixed
Decimal Places 3
Limit to List ???
However, the combo does not display the 3rd decimal places.
Presumably the bound column is visible.

I'm not quite sure why a combo is appropriate here. There is only a fixed
number of possible choices to choose from? So the RowSource of the combo
supplies the possible choices?

Suggestions.
1. Open the table in design view, and change the field's Type to Currency.
This will prevent the rounding errors inherent in the floating point types.

2. Explicitly typecast the query field, i.e. use:
CCur(Nz(Sum([Length]),0))
Explanation of why:
http://members.iinet.net.au/~allenbrowne/ser-45.html

That should take care of the rounding errors, and the possibility for
misunderstanding the data type. The combo should then be able to follow your
instructions for formatting the data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried all you sugested, but it doesn't work!

 

[Geo]

I use a combo because the data for it must respect some conditions ( is
like:
SELECT DISTINCT stoc1.Lenght FROM stoc1 WHERE
(((stoc1.Dimensions)=[cboDimensions]) AND ((stoc1.Name)=[cboName]) AND
((stoc1.Quality)=[cboQuality]));
where stoc 1 is the query where the data is taken from, the other combo
boxes are from the curent form :[cboName] has the row surce SELECT DISTINCT
stoc1.Name FROM stoc1;
[cboDimensions] : SELECT DISTINCT stoc1.Dimensions FROM stoc1 WHERE
(((stoc1.Name)=[cboName]));

cboQuality: SELECT DISTINCT stoc1.Quality FROM stoc1 WHERE
(((stoc1.Dimensions)=[cboDimensions]) AND ((stoc1.Name)=[cboName]))

I tried your advice (using CCur(Nz(Sum([Length]),0)) ), but in the combo
box it still doesn't appear the right value(the number is still rounded at 2
decimals even in the query has 3).
It seems I'm stucked.


P.S. What I want to do is to extract some data from a stock and use it in a
table of withdrawals.
The query STOC:
SELECT DISTINCT [stoc1-0].Cod_Material, [stoc1-0].Denumire,
[stoc1-0].Dimensiuni, [stoc1-0].Calitatea, CCur(nz(Sum([Lungime(m)]),0)) AS
Lungime, Sum([stoc1-0].[Cantitate(kg)]) AS [SumOfCantitate(kg)],
Sum([stoc1-0].Numar) AS SumOfNumar, [stoc1-0].ID_den,
[stoc1-0].ID_dimensiuni, [stoc1-0].Cod_Calit, [stoc1-0].Pret,
Sum([stoc1-0].[Pret total]) AS [SumOfPret total]
FROM [stoc1-0]
GROUP BY [stoc1-0].Cod_Material, [stoc1-0].Denumire, [stoc1-0].Dimensiuni,
[stoc1-0].Calitatea, [stoc1-0].ID_den, [stoc1-0].ID_dimensiuni,
[stoc1-0].Cod_Calit, [stoc1-0].Pret
ORDER BY [stoc1-0].Denumire, [stoc1-0].Calitatea;
The table Withdrawals has the fields:
ID autonumber
Data date/time default valueate()
Denumire text
Dimensiuni text
Calitate text
Moneda text Default value: "local currency"
Pret currency decimals:3
UM text Default value:"m"
CANTITATE LIMITA (AVAILABLE QUANTITY) Currency Format:Fixed
Decimal places 3
Cantitate double standard
- the withdrawed quantity

 

[Allen Browne]

Not sure what else to suggest.
The combo is displaying the value correctly here.

Usually when JET fails to respect the Format property of a control it is
because it does not understand the data type, but we have taken all possible
steps to help it here.

 

[DebbieG]

Have you tried replacing the field inside the Row Source with
Format([fieldname],"0.000") ? I had a similar problem with a combo box once
and it seemed like this is what fixed it.

HTH,
Debbie

I use a combo because the data for it must respect some conditions ( is
like:
SELECT DISTINCT stoc2.Lenght FROM stoc2 WHERE
(((stoc2.Dimensions)=[cboDimensions]) AND ((stoc2.Name)=[cboName]) AND
((stoc2.Quality)=[cboQuality]));
where stoc 2 is the query where the data is taken from, the other combo
boxes are from the curent form :[cboName] has the row surce SELECT DISTINCT
stoc2.Name FROM stoc2;
[cboDimensions] : SELECT DISTINCT stoc2.Dimensions FROM stoc2 WHERE
(((stoc2.Name)=[cboName]));

cboQuality: SELECT DISTINCT stoc2.Quality FROM stoc2 WHERE
(((stoc2.Dimensions)=[cboDimensions]) AND ((stoc2.Name)=[cboName]));

I tried your advice (using CCur(Nz(Sum([Length]),0)) ), but in the combo
box it still doesn't appear the right value(the number is still rounded at 2
decimals even in the query has 3).
It seems I'm stucked.

Okay, let's start from the top. You have a field in a table that is:
Name Length
Type Number
Size Double

In a query, you have a field that uses:
Sum(Length)
It does not matter what properties you set in the query.

In a form, you have a combo box that has these properties:
Control Source SumOfLength
Format Fixed
Decimal Places 3
Limit to List ???
However, the combo does not display the 3rd decimal places.
Presumably the bound column is visible.

I'm not quite sure why a combo is appropriate here. There is only a fixed
number of possible choices to choose from? So the RowSource of the combo
supplies the possible choices?

Suggestions.
1. Open the table in design view, and change the field's Type to Currency.
This will prevent the rounding errors inherent in the floating point types.

2. Explicitly typecast the query field, i.e. use:
CCur(Nz(Sum([Length]),0))
Explanation of why:
http://members.iinet.net.au/~allenbrowne/ser-45.html

That should take care of the rounding errors, and the possibility for
misunderstanding the data type. The combo should then be able to follow your
instructions for formatting the data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried all you sugested, but it doesn't work!

 

[Dave C.]

I have the same problem with a drop down list. Did you ever get it figured
out? When I chance the decimals to "three" and "fixed" in the SQL editor,
close and reopen the editor, the decimal field is set back to blanks? The
field in the table is set to "three" decimals and "fixed". It displays
correctly everywhere, except the drop down box.