Query based upon another query not returning decimal data

[RDunlap]

I have Qry2 based upon Qry1 and that query is based on a linked table in SQL
2005. Fld1 and Fld2 are in the table and defined as decimal(28,18) in SQL.

Qry1 returns records containing Fld1 & Fld2 without any problems.

Qry2 is a Crosstab query performing a Group By / Row Heading on Fld1 and a
sum value on Fld2.

Qry2 gets an error on Fld1 stating "Invalid Precision for decimal data
type". If I use the CDec function on it, the error for this field does not
appear any more - but then after some processing, I get this error on Fld2:
"Invalid scale for decimal data type".

If I use the CDec function on this field as well, the query will run to
completion, but fld1 and fld2 are always zero, although I know they have
values.

I have tried playing with the properties for the columns in Qry2, but it
does not change the output, it's always zero. The values in these fields in
the linked table are weights and are generally in the 200 lb range and always
less than 400 lbs for these particular queries and the queries return 350
rows.

Any ideas why I am even having to use the CDec function in the first place
and why once I do, I only get zero's?

Thanks in advance for any assistance.

 

[Pieter Wijnen]

Just a thought.
Try using the CDbl or Val Function instead.
The root of the problem is that Access uses a variant, not a numeric,
datatype for decimal data.

HtH

Pieter

 

[RDunlap]

I guess that makes sense now that I think about the problems Access has with
decimal data type, and it did work.

Thanks for the quick reply.