change from decimal to double

[Elsie]

hi, I have a query with fields: acct, amt, dept-code. I have a pivot table
based on this query in access. but the data is not accurate until I change
the query into a make-table query and based the pivot table on the table.

I changed the property of amt in the table to double and the pivot table
works fine.

illustration:

when property of amt is decimal: 17512.92 becomes 17512.00 and 12516.12
becomes 12514.
when property of amt is double: 17512.92 is displayed as 17512.92 and
12516.12 is displayed 12516.12.

hence, I need a way to change the property of the field amt in the table
without the user going into the db & touch the table design.

or should I just change the make-table query into append query?
is there any way I can do something like Double(amt) in the query {since
int(amt) works in queries}?

pls help

 

[Allen Browne]

To force the field to be of type Double, use:
CDbl([Amt])
That will fail if there are nulls, so you probably need to use:
CDbl(Nz([Amt], 0))

Ultimately, defining the table the way you want it and then using an Append
query is proably the best result. If you are deleting and recreating this
table repeatedly, you can just empty the saved table with:
dbEngine(0)(0).Execute "DELETE FROM MyTable;", dbFailOnError

More information on type casting query fields:
http://members.iinet.net.au/~allenbrowne/ser-45.html