Problem with calculation in a query

[caz]

Hello

Can any body advise me.

I am running a simple sum

sum ([SUBTOTAL]/1000)

it does not give me the results I am after for example:

8.32 /1000 = 0.0832 - This is the desired result

I am getting

8.32/1000 = 0

It does note help if I change the decimal places.

What am I missing?

Thanks

 

[caz]

If I change the properties in the query grid on the calculation I do not have double tyoe in the list of formats and Currency does not work.

Any other suggestions?

 

[Rich]

Change the data type of the field in question in the table

 

[neileg]

What are you trying to do? If this is a totals query, the SQL will read:
sum ([SUBTOTAL]/1000) AS MyCalcField
This assumes that [SUBTOTAL] is a field in your table. Despite the contributions of my learned colleagues, I can't see how the underlying datatype in the table makes any difference to this calculation (providing it's numeric and not text). I can't replicate your problem with any numeric datatype.

However, if this is what you are trying to do, your example calculation doesn't make a lot of sense. Are you using this formula in a new calculated field? If you are, the SUM isn't a valid calculation.

Please explain.

 

[caz]

I now have it working Thanks for suggestions.

 

[neileg]

I see where you are coming from, Pat. However, a bit of empirical experience, followed by a quick test before I posted this, shows that there's no problem in using integers in a calculation that produces a decimal result.