Invalid precision for decimal data type

[chosensilver]

I have an Access database front-end that connects to an SQL server through linked tables. Within this database there is a report that is based on a query. When I try to run the report, I get the error mentioned in the subject line. If I run the query by itself I do not havw the issue. There is only one calculation done on the report, so I believe the calculation to be giving this error, but how can I make it work properly? I have seen a few posts on the net saying to alias calculated columns like that, but I don;t know of any way to alias a calculated value in a text box on a report. Any help would be greatly appreciated. If this helps at all...the data used to be stored directly in the Access database, and I migrated just the tables to the SQL server. The report has not worked since then.

 

[belovedcej]

sounds like the datatype may need to be changed. I know that the integer types between Access and SQL are different - For an int in SQL you need a LONG in Access. When converting between decimal numbers, it is easy to lose precision, and most systems get a little picky about that. Check out the data type it used to be in Access and check out what it transfered as. Look up their size (probably easy to find online) and you may find that your error is that either Access or SQL is using a type that isn't big enough to hold all the numbers.

 

[chosensilver]

I have been down that road already, and like I mentioned the query that the report is based on runs without issue. There is a section on the report where it is doing a calculation based on the values from the query, and thta is what is casing the error to occur. I have tried typecasting everything, but that did not help either.

 

[belovedcej]

Ah - my apologies - I had thought you were referring to an SQL view running correctly - my mind is in ADP world.

I only have one other idea, then - and you probably already tried it. . .

I would mess with the Format and Decimal places options on the properties tab of the control itself to see if telling it explicitly what kind of number it is, or how many decimal points it should have, would help.

 

[chosensilver]

You are correct...I have tried that. This is very frustrating.

 

[chosensilver]

As most of the replies elsewheer on the net have to do with just aliasing the calculated field...does anyone know how to create an alias for a summary field on a report.

The following will fail:

=(Sum([BlkDen]*[SumOf#Loads])/Sum([SumOf#Loads])) as some_name

so will this:

some_name: Sum([BlkDen]*[SumOf#Loads])/Sum([SumOf#Loads])

 

[chosensilver]

Nobody have any ideas?

 

[chosensilver]

I guess I figured out my own solution. I just changed the query to be an append query that selected all the data into a temp table. I based the report from the temp table rather than the query and it works fine now.

 

[Remou]

Have you tried posting in the JET SQL forum?
Microsoft: Access Queries and JET SQL Forum
forum701