Invalid scale for decimal data type error

[angelandgreg]

I have a query that is to produce the final result from two other queries where these queries Groups from details ...

hope i made sense there?

And for some reason on my final query with a Group I keep getting this "Invalid scale for decimal data type" on one column.

The QOH column. The QOH is an avg from the details query then is summed in another grouped query and then finally is just a Group By column in my final query and this is where I get the message.

When I run the details and the summed queries, the QOH produces the results. I am not sure why it won't on my final query and getting this error message.

please help

 

[TheAceMan1]

How are ya angelandgreg . . .

In the table try setting the data type for QOH to [blue]Double[/blue] or [blue]Single[/blue] . . .

See Ya! . . . . . .

 

[angelandgreg]

Okie dokie and you?

Ok, not sure where to change the QOH to Double or Single.

From the query I went to properies and the Format option is to change it to Fixed and Decimal Place to 0 and I am still getting the Invalid message.

 

[angelandgreg]

I think it's these fields that's causing the error??
Sum([OffTheRadar-000and005].[QOP]+IIf([QOnP] Is Null,0,[QOnP])) AS QtyP - this is an Expression

IIf([QOnH] Is Null,0,[QOnH]) AS QCHINA - this is a Group By


Although when I remove the QOH field they work??

 

[TheAceMan1]

angelandgreg . . .

Before we get too far with this, take a look below at the definition of [blue]Decimal[/blue] data type as prescribed by access in VBE help. Bear in mind your problem while you do:

[blue]Decimal Data Type:

Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10. The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.

Note At this time (access 2K) the Decimal data type [purple]can only be used within a Variant[/purple], that is, you cannot declare a variable to be of type Decimal. [purple]You can, however, create a Variant whose subtype is Decimal using the [blue]CDec[/blue] function.[/purple][/blue]

There is also the matter of [purple]coercion[/purple] . . . where [purple]intermediate results can be coerced into different data types![/purple] For instance . . . depending on how their added, an Integer data type added to an Decimal datatype can produce a result of either data type!

The point being . . . you need to run the subqueries seperately and see if the results are [blue]suitable for Decimal data type[/blue]. I suspect somewhere along the line you'll come across the gremlin and know what to do.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[angelandgreg]

TheAceMan1,

thanks for your help. I ended starting from scratch and all is well!