Microsoft Access Overflows When I create a link table from SQLServer and try to calculate it in a query

A table linked to Microsoft Access from SQLServer
SQL calculates and tries to display, causing overflow
This will result in an error.

I was able to calculate normally when using the local table without linking.
Link will result in an error.

Below is the SQL used.

SELECT [category], SUM (CLNG([Width] * [High] * [Length] * [Number] * [Price]) / 1000000000)) AS [Total] FROM ([M_Material] WHERE [id] = 187 GROUP BY [category]

Maybe it's because of the data that shows the volume in mm, but it seems to be quite a large number, so I don't know what to do.

Could you give me a solution?

Thank you for your cooperation.

sql-server ms-access

2022-09-30 19:49

1 Answers

I don't know how to issue SQL, but for now, I'll send you the formula

 [width] / 1000000000.0 * [high] * [length] * [number] * [price]

I think it will work if you do so.
However, since we are CLNG, it is assumed that this formula falls within the VBA Long range.
The reason for the error is that in SQL Server, the operation between int types is int type.
Literals with decimal points are considered the decimal (numerical) type, so they can be calculated beyond the int type.

Also, CLNG is not a round-off, but a round-up called bank round-up.
By the way, CLNG is a function of VBA, so if you process a large amount of data, there is a high possibility that performance will not be possible.

2022-09-30 19:49

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656