Different results from Stored Query and VB SQL Retrieval

[Golom]

I have two tables as follows.

TABLE CANBE

NAME		Seq	Canbe
ACCEPT_RET	1	STOCK
ACCEPT_RET	2	SUBDETAIL
ACCEPT_RET	3	SUBDEPT
ACCEPT_RET	4	DEPT
ACCEPT_RET	5	COMPANY
... etc.

TABLE SET

NAME		LEVEL		VALUE	SET
ACCEPT_RET	DEPT		34	No
ACCEPT_RET	DEPT		37	No
ACCEPT_RET	DEPT		99	No
ACCEPT_RET	SUBDEPT		77	No
ACCEPT_RET	SUBDETAIL	A	No
... etc.

And I have a stored query called "GP" like this

PARAMETERS [Policy_Name] Text, [Sequence] Long;
SELECT C.Name, C.Canbe, C.Seq, S.Level, S.Value, S.Set
FROM Canbe As C INNER JOIN Set As S ON (C.Canbe = S.Level) AND (C.Name = S.Name)
WHERE C.P_Name = [Policy_Name] AND
C.Seq >= [Sequence] AND
S.Level <> 'COMPANY'
ORDER BY C.Seq;

When I run this from VB with the following code (DAO),

Dim qdf As DAO.QueryDef
Set qdf = dbControl.QueryDefs.Item(&quot;GP&quot
qdf(&quot;Policy_Name&quot = &quot;ACCEPT_RET&quot;
qdf(&quot;Sequence&quot = 3
Set rsCanbe = qdf.OpenRecordset(dbOpenDynaset)

I get

Name		Canbe		Seq	Level		Value	Set
ACCEPT_RET	SUBDEPT	3	SUBDEPT		77	No
ACCEPT_RET	DEPT		4	DEPT		99	No
ACCEPT_RET	DEPT		4	DEPT		37	No
ACCEPT_RET	DEPT		4	DEPT		34	No

and all is as it should be.

However, if I run it from VB directly (i.e. not as an existing stored query) like this

Set rsCanbe = db.OpenRecordset( _
&quot;SELECT C.Name, C.Canbe, C.Seq, S.Level, S.Value, S.Set
FROM Canbe As C INNER JOIN Set As S ON (C.Canbe = S.Level) AND (C.Name = S.Name)
WHERE C.P_Name = 'ACCEPT_RET' AND
C.Seq >= 3 AND
S.Level <> 'COMPANY'
ORDER BY C.Seq;&quot;, dbOpenDynaset)

It Returns

NAME		Canbe		Seq	LEVEL		VALUE	SET
ACCEPT_RET	SUBDETAIL	2	SUBDETAIL	A	No
ACCEPT_RET	SUBDEPT		3	SUBDEPT		77	No
ACCEPT_RET	DEPT		4	DEPT		99	No
ACCEPT_RET	DEPT		4	DEPT		37	No
ACCEPT_RET	DEPT		4	DEPT		34	No

The first record shouldn't be there because the value of &quot;Seq&quot; is less than 3, contrary to the &quot;C.Seq >= 3&quot; in the WHERE clause. Is this just one of those &quot;weird computer things that happens all the time&quot; or am I making a logical error somewhere?

If I copy and paste this SQL into an Access query, it produces the correct results.

 

[MarkSweetland]

May need to segregate the boolean parts of the where clause. Try putting parenthesis around the comparisons:


WHERE (((C.P_Name)=&quot;ACCEPT_RET&quot AND ((C.seq)>=3) AND ((S.Level)<>'COMPANY'))


Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[Golom]

Thanks Mark. I'll give it a try.