1

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?

Riku Das
  • 91
  • 1
  • 14
  • I would suggest to move complex queries like this to the Sql Server backend now. It seems like a waste of time trying to get it to work when eventually you will probably move it anyway for performance reasons. – Andre Apr 28 '19 at 22:30
  • As I stated, the second part of my project is to move these queries, onto SQL Server via the use of views and pass-through quwries etc. I have over 1700 access queries so moving them onto SQL is a project in it itself. Also priotising what needs moving needs to be decided prior to this. I need solutions to these problems to ensure I have a working product and resolve all the issues with the current application (which was not built by me but rather my predecessor) – Riku Das Apr 28 '19 at 23:48

3 Answers3

2

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

SunKnight0
  • 3,331
  • 1
  • 10
  • 8
1

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.

Van Ng
  • 773
  • 1
  • 7
  • 17
  • He does two operations in one query. If you're good at it, that's ok. And if you're novice it is better to decompose – Van Ng Apr 28 '19 at 22:48
1

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

Riku Das
  • 91
  • 1
  • 14