asked on

Using an MS Access SQL Pass-through query on a Sage 50 Accounts Stock table returns unexpected results, why?

Hi everyone,

I hope you can help with this...

I have the following SQL in a pass-through query to the Sage 50 stock table:-

SELECT STOCK.STOCK_CODE, STOCK.DESCRIPTION, STOCK.COMPONENT_CODE_1, STOCK.COMPONENT_QTY_1 FROM STOCK WHERE (((STOCK.COMPONENT_QTY_1)>0)) AND (((STOCK.COMPONENT_CODE_1) Is Not Null));

It's working correctly with "STOCK.COMPONENT_QTY_1)>0" but completely ignoring "STOCK.COMPONENT_CODE_1) Is Not Null" and showing empty fields. What am I doing wrong? I tried with a select query to start with, but I got an error message, the same as this user, which lead me to use a pass-through query instead. But now I'm stuck...

Thanks in advance,
Beckey.

Fabrice Lambert

What do you mean by "empty fields" ?

Are these null values (it shouldn't) ?
Or zero length strings (not the same as null) ?

In the 2nd case, I would say that the query retrieve data as expected.

Beckey Boyles

ASKER

Thanks for the clarification, what command should I be using on an empty text field then please?

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

not familiar with Sage 50,

But in SQL Server, to test for Null or blank you would use something like:

WHERE (STOCK.COMPONENT_QTY_1>0)
AND IsNull(STOCK.COMPONENT_CODE_1, '') <> ''

Open in new window

John Tsioumpris

Just for testing construct your passthrough without the null condition

SELECT STOCK_CODE
	,DESCRIPTION
	,COMPONENT_CODE_1
	,COMPONENT_QTY_1
FROM STOCK
WHERE COMPONENT_QTY_1 > 0

Open in new window

Then feed this passthrough to a Access as source for Query Designer to check what values STOCK.COMPONENT_CODE_1 holds...just put the Null criteria on the Access query

Beckey Boyles

ASKER

Thank you Fabrice & Dale.

I used this in the end and it works beautifully!

SELECT STOCK_CODE,
DESCRIPTION,
COMPONENT_CODE_1,
COMPONENT_QTY_1
FROM STOCK
WHERE (COMPONENT_QTY_1 > 0 AND STOCK.COMPONENT_CODE_1 <> '');

Thank you :)

Beckey Boyles

ASKER

Thanks John, sorry, your answer wasn't showing when I posted just now!

Beckey Boyles

ASKER

I went with this in the end...

SELECT STOCK_CODE,
DESCRIPTION,
COMPONENT_CODE_1,
COMPONENT_QTY_1
FROM STOCK
WHERE (COMPONENT_QTY_1 > 0 AND STOCK.COMPONENT_CODE_1 <> '');

John Tsioumpris

np Beckey ...glad its sorted out...