MakeTable Query -- Field Must be "Double" vs. "Decimal"

[Tom]

In a "MakeTable" query, I use an expression that calculates the following
(sample):

Total: ([X]*0.6+[Y]*0.4)

When I execute the query, field "Total" always becomes a "Decimal" data
type.

Does anyone know how I modify the query to ensure the data type will always
be "Double" (3 decimals) for field "Total" (once the MakeTable query has
been executed)?

Thanks,
Tom

 

[Allen Browne]

Try:
Total: CDbl(Nz([X] * 0.6 + [Y] * 0.4, 0))

That should return a Double type.

You will still need to open your table in design view, and set the Format to
Fixed and the Decimal Places to 3 if you want it displayed that way. (Note
that these settings only affect the way the data is displayed, not how it is
stored.)

 

[Tom]

Allen:

Thanks, that works perfect now!

Tom

Try:
Total: CDbl(Nz([X] * 0.6 + [Y] * 0.4, 0))

That should return a Double type.

You will still need to open your table in design view, and set the Format to
Fixed and the Decimal Places to 3 if you want it displayed that way. (Note
that these settings only affect the way the data is displayed, not how it is
stored.)

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

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

In a "MakeTable" query, I use an expression that calculates the following
(sample):

Total: ([X]*0.6+[Y]*0.4)

When I execute the query, field "Total" always becomes a "Decimal" data
type.

Does anyone know how I modify the query to ensure the data type will
always
be "Double" (3 decimals) for field "Total" (once the MakeTable query has
been executed)?