Right join not working now

M

Mark

Hello all,

I have been running this dB for over 2 years now without much issue, but for
some reason now I get a message box that reads "Invalid Operation" whenever I
run this query. I can change it to and inner join and it works fine. Is
there something someone might had switch in the options to cause this, or
what else could cause this to work for years then not?

Mark

SELECT qrProTotal.SKU, tbSkuSum.Qty, Nz(([qty]),0) AS [PickPro Qty],
qrProTotal.SumOfQTY AS [Provia Qty], tbSkuSum.Desc, Nz(([qty]),0)-[sumofqty]
AS Variance,
IIf((Nz(([qty]),0)-[sumofqty])<0,(Nz(([qty]),0)-[sumofqty])*-1,0) AS Low,
IIf((Nz(([qty]),0)-[sumofqty])>0,(Nz(([qty]),0)-[sumofqty]),0) AS High,
tbSkuSum.date
FROM tbSkuSum RIGHT JOIN qrProTotal ON tbSkuSum.Item = qrProTotal.SKU
WHERE (((Nz(([qty]),0)-[sumofqty])<>0));
 
M

Mark

Sorry, the SQL below is not the one in issue it is built over one that is
(below).

SELECT qrProTotal.SKU, tbSkuSum.Qty AS [PickPro Qty], qrProTotal.SumOfQTY AS
[Provia Qty], tbSkuSum.Desc, Nz(([qty]),0)-[sumofqty] AS Variance,
IIf((Nz(([qty]),0)-[sumofqty])<0,(Nz(([qty]),0)-[sumofqty])*-1,0) AS Low,
IIf((Nz(([qty]),0)-[sumofqty])>0,(Nz(([qty]),0)-[sumofqty]),0) AS High
FROM tbSkuSum RIGHT JOIN qrProTotal ON tbSkuSum.Item = qrProTotal.SKU;
 
M

Michel Walsh

Is qrProTotal a query? Is it running fine?


Vanderghast, Access MVP


Mark said:
Sorry, the SQL below is not the one in issue it is built over one that is
(below).

SELECT qrProTotal.SKU, tbSkuSum.Qty AS [PickPro Qty], qrProTotal.SumOfQTY
AS
[Provia Qty], tbSkuSum.Desc, Nz(([qty]),0)-[sumofqty] AS Variance,
IIf((Nz(([qty]),0)-[sumofqty])<0,(Nz(([qty]),0)-[sumofqty])*-1,0) AS Low,
IIf((Nz(([qty]),0)-[sumofqty])>0,(Nz(([qty]),0)-[sumofqty]),0) AS High
FROM tbSkuSum RIGHT JOIN qrProTotal ON tbSkuSum.Item = qrProTotal.SKU;


Mark said:
Hello all,

I have been running this dB for over 2 years now without much issue, but
for
some reason now I get a message box that reads "Invalid Operation"
whenever I
run this query. I can change it to and inner join and it works fine. Is
there something someone might had switch in the options to cause this, or
what else could cause this to work for years then not?

Mark

SELECT qrProTotal.SKU, tbSkuSum.Qty, Nz(([qty]),0) AS [PickPro Qty],
qrProTotal.SumOfQTY AS [Provia Qty], tbSkuSum.Desc,
Nz(([qty]),0)-[sumofqty]
AS Variance,
IIf((Nz(([qty]),0)-[sumofqty])<0,(Nz(([qty]),0)-[sumofqty])*-1,0) AS Low,
IIf((Nz(([qty]),0)-[sumofqty])>0,(Nz(([qty]),0)-[sumofqty]),0) AS High,
tbSkuSum.date
FROM tbSkuSum RIGHT JOIN qrProTotal ON tbSkuSum.Item = qrProTotal.SKU
WHERE (((Nz(([qty]),0)-[sumofqty])<>0));
Click to expand...
Click to expand...
 
Top