Another problem with sum

[Accessnewb]

I'm trying to create an extended price by making query on a form

Basically, I have

SELECT DISTINCTROW [Siding Details].*, ([Quantity]*[Price]) AS ExtPrice
FROM [Siding Details];

My ExtPrice field is bound to ExtPrice in the table.

Another irritating problem I'm having is..

I have an AfterUpdate event with the following

Me.Price = Me.Product.Column(2)
Me.Quantity.SetFocus

Let's say the price of the item in Product is $3.25. The price that gets generated in [Price] is $3.00. It either rounds up or rounds down the value. The [Price] field is bound to [Price] in the table.

If I simply use an expression =product.column(2) in the [Price] field it transfers the decimals correctly but I would rather not go this route as I would like to bind the field.

Hoping someone can give me a hand with these 2 issues. I've searched everywhere. I've looked in a couple other order entry test databses and copied what was done with no success. I know I must be missing something but can't figure it out.

[Stewart Ross]

...I have an AfterUpdate event with the following

Me.Price = Me.Product.Column(2)
Me.Quantity.SetFocus

Let's say the price of the item in Product is $3.25. The price that gets generated in [Price] is $3.00. It either rounds up or rounds down the value. The [Price] field is bound to [Price] in the table. ...

The rounding error appears to be some kind of type conversion issue. I can't think offhand of a reason why in this case, as Access usually converts 'on the fly' dependent on the calculation involved. (Bear in mind that combo boxes return text values in their columns, not type-bound values.)

On the surface, it appears that an integer conversion is taking place (hence the dropping of the decimals).

Assuming that your [price] field is either of type currency or double, try an explicit type conversion when referring to the combo column involved:

Me.Price = CCurr(Me.Product.Column(2)), or
Me.Price = CDbl(Me.Product.Column(2))

This will at least ensure that [price] is fed with a value of the correct type, which can only help in eliminating possible causes of the error.

Regards

Stewart

[Accessnewb]

Me.Price = CCurr(Me.Product.Column(2)) didn't work at all and cause the debugger to pop up.

Me.Price = CDbl(Me.Product.Column(2)) just gave me the same results I was getting previously

[Stewart Ross]

Me.Price = CCurr(Me.Product.Column(2)) didn't work at all and cause the debugger to pop up.

Me.Price = CDbl(Me.Product.Column(2)) just gave me the same results I was getting previously

OK. Going back to the SELECT statement in your original question, you said this was the code:

 
SELECT DISTINCTROW [Siding Details].*, ([Quantity]*[Price]) AS ExtPrice
FROM [Siding Details];
 

Is what you are doing that you are:
1. using a userform to select a price from a combo box, which is bound to the Price field (presumably so that you can enter and vary a price value for a customer on demand)
2. after entering the price, your user then enters a quantity,
3. then you calculate an ExtPrice using the Select statement?

I am not clear as to why you would do this, as you could have a calculated control on your form which does this for you. Anyway, I would ask you to clarify that I have interpreted this correctly?

It is important, because if [Price] is a double there will be no decimal truncation involved if you explicitly type-convert the combo result feeding it to double. Also, if ExtPrice and Price are doubles then [Quantity] * [Price] will generate a double as a result, not an integer value.

Please confirm the types of [price] and [ExtPrice] in your underlying table. I would also like to see the SQL code for your combo box, to try to resolve this one fully if possible.

Regards

Stewart

[NeoPa]

I suspect [Quantity] is an integer field. Try casting that to Double in the SQL.

SELECT DISTINCTROW [Siding Details].*, (CDouble([Quantity])*[Price]) AS ExtPrice
FROM [Siding Details]