CODEWITHSUNDEEP
sql-serverdatabasems-accessdatabase-migration
Riku Das
Riku Das

Reputation: 91

Unexplained 'Invalid Operation' error in Access query with SQL backend

I am trying to migrate the entire backend of an Access application onto SQL Server. The first part of my project involves moving all of the tables whilst making minimum changes after the migration (no SQL Views, Pass-through queries etc. yet).

I have two queries in particular that I am using here:

ProductionSystemUnAllocatedPurchases - Which executes and returns a resultset successfully.

ProductionSystemUnAllocatedPurchases_Screenshot This is the full formula (sorry its extremely complex) for QtyAvailableOnPurchase:

QtyAvailableOnPurchase: I believe this field could be the problem here?

IIf((IIf([Outstanding Qty]>([P-ORDER-T with Qty Balance]![QTY]-[SumOfQty]), 
([P-ORDER-T with Qty Balance]![QTY]-[SumOfQty]),[Outstanding Qty]))>0, 
(IIf([Outstanding Qty]>([P-ORDER-T with Qty Balance]![QTY]-[SumOfQty]),([P- 
ORDER-T with Qty Balance]![QTY]-[SumOfQty]),[Outstanding Qty])),0)

ProductionSystemUnAllocatedPurchasesTotal - Which gives an 'Invalid Operation' error message

ProductionSystemUnAllocatedPurchasesTotal_Screenshot

Now the strange thing for me is that the first query works perfectly fine, but the second which uses the first as a source table, gives me this error message when executing. This query works perfectly fine with an access backend, but fails with SQL Server tables. Any Ideas?

Upvotes: 1

Views: 2166

Answers (3)

Riku Das
Riku Das

Reputation: 91

I have managed to find a solution to this error. It seems that the problem is not so much with the query but rather the data type on SQL Server. SQL Server Migration Assistant (SSMA) automatically maps any Number (Double) fields to float on SQL Server. This mapping needed manually changing to Decimal.

Now according to this SO post, Decimal is the preferred for its precision up to 38 points (which is more than enough for my application), While float allows more than this, the data is stored in approximates.

Source: Difference between numeric, float and decimal in SQL Server

Upvotes: 1

Van Ng
Van Ng

Reputation: 803

My approach is to decompose queries. Create two queries :

  1. First query selects needed data

  2. Second query applies group operations (e.g. Sum)

You'll get easy way to check every step.

Upvotes: 1

SunKnight0
SunKnight0

Reputation: 3351

Can QtyAvailableOnPurchase be NULL? That would explain why Sum fails. Use Nz(QtyAvailableOnPurchase,0) instead.

Upvotes: 2

Related Questions