Access 2000 Truncating Values in Make Table Query

  • Thread starter Thread starter mcl
  • Start date Start date
M

mcl

The title about says it all. If I run the query as just a query everything
is fine. The values are to one decimal point. If I convert it to a make
table query the values are truncated. If I look at the table in design mode,
the truncated values are listed as decimal. There are some fields listed as
double. They are fine. The sample code from the fields that are truncating
looks like this.
Jan_in: Avg(IIf([Jan]=-9999,Null,IIf([Jan]=-8888,0.0004,[Jan]/254)))
The data is precip data for Jan. There are columns for all months. The imput
data is in mm*10 where missings are -9999 and traces are -8888. I convert
the missings to nulls and the traces to an arbitrarily small number. In this
case .0004. The /254 converts the mm*10 to inches. I have them formated as
fixed to one decimal place. When I just run the query it displays fine.
The last fields sums the monthly means. It looks like this:
Annual_in:
[Jan_in]+[Feb_in]+[Mar_in]+[Apr_in]+[May_in]+[Jun_in]+[Jul_in]+[Aug_in]+[Sep
_in]+[Oct_in]+[Nov_in]+[Dec_in]
It's also fixed to 1 decimal place for the display.
When I run it as a make query the individual months truncate and shows up as
decimal in design mode. The Annual_in does not truncate. In design mode it's
double. In fact it displays all digits, not just to 1 decimal place.
 
When I read my post I wasn't sure if I was clear about the truncation. The
truncated fields show up as integers only.
 
When Access creates a table (Make Table Query) it has to guess at what the
data types are. Try specifically converting them to the type you want using
CDbl(), CSng(), etc.

Kelvin

mcl said:
When I read my post I wasn't sure if I was clear about the truncation. The
truncated fields show up as integers only.

mcl said:
The title about says it all. If I run the query as just a query everything
is fine. The values are to one decimal point. If I convert it to a make
table query the values are truncated. If I look at the table in design mode,
the truncated values are listed as decimal. There are some fields listed as
double. They are fine. The sample code from the fields that are truncating
looks like this.
Jan_in: Avg(IIf([Jan]=-9999,Null,IIf([Jan]=-8888,0.0004,[Jan]/254)))
The data is precip data for Jan. There are columns for all months. The imput
data is in mm*10 where missings are -9999 and traces are -8888. I convert
the missings to nulls and the traces to an arbitrarily small number. In this
case .0004. The /254 converts the mm*10 to inches. I have them formated as
fixed to one decimal place. When I just run the query it displays fine.
The last fields sums the monthly means. It looks like this:
Annual_in:
Click to expand...
[Jan_in]+[Feb_in]+[Mar_in]+[Apr_in]+[May_in]+[Jun_in]+[Jul_in]+[Aug_in]+[Sep
_in]+[Oct_in]+[Nov_in]+[Dec_in]
It's also fixed to 1 decimal place for the display.
When I run it as a make query the individual months truncate and shows
Click to expand...
up
as
decimal in design mode. The Annual_in does not truncate. In design mode it's
double. In fact it displays all digits, not just to 1 decimal place.
Click to expand...
Click to expand...
 
Back
Top