strange behavior on query result

[Sunny]

I am writing query to find out client list who has not placed order between
certail dates, I tried following query on SQL server and in access, it re
turns me results on sql server but does not return anything in access. Can
some one explain me why is this strange behavior in access?

SQL Query
select * from Clients where clientid not in
(SELECT Clientid FROM purchase WHERE purdate >= '12/31/2004' AND purdate <=
'1/1/2005' )

Access query
select * from Clients where clientid not in
(SELECT Clientid FROM purchase WHERE purdate >= #12/31/2004# AND purdate <=
#1/1/2005# )

I do not have purchase records between those dates, I get all clients in SQL
query but nothing in Access.

 

[MGFoster]

I am writing query to find out client list who has not placed order between
certail dates, I tried following query on SQL server and in access, it re
turns me results on sql server but does not return anything in access. Can
some one explain me why is this strange behavior in access?

SQL Query
select * from Clients where clientid not in
(SELECT Clientid FROM purchase WHERE purdate >= '12/31/2004' AND purdate <=
'1/1/2005' )

Access query
select * from Clients where clientid not in
(SELECT Clientid FROM purchase WHERE purdate >= #12/31/2004# AND purdate <=
#1/1/2005# )

I do not have purchase records between those dates, I get all clients in SQL
query but nothing in Access.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are you using an .adp file or an .mdb file?

If an .adp file, don't use "#" as the date delimiter.

If an .mdb file, is the query a local query or a pass-thru query? If a
pass-thru query use single-quotes as the date delimiter. If a local
query the # date delimiter should work.

The query may run faster if like this (SQL Server syntax):

SELECT C.*
FROM Clients AS C LEFT JOIN Purchase As P
On C.ClientID = P.ClientID
WHERE P.purdate NOT Between '20041231' And '20050101'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQh+UOoechKqOuFEgEQIJwQCeJuc22Rmn8srsMV1a9C7VDh88kFYAniJB
BoLSmxDvg6vbapg6Nvr5xNvp
=Lw3U
-----END PGP SIGNATURE-----

 

[Guest]

Sunny --

One thing you could try is using the BETWEEN...AND statements in Access as
follows:

select * from Clients where clientid not in _
(SELECT Clientid FROM purchase WHERE purdate BETWEEN #12/31/2004# AND _
#1/1/2005#);

It also may help to ensure that purdate is in Date format and not String etc.

HTH!

 

[Chris2]

I am writing query to find out client list who has not placed order between
certail dates, I tried following query on SQL server and in access, it re
turns me results on sql server but does not return anything in access. Can
some one explain me why is this strange behavior in access?

SQL Query
select * from Clients where clientid not in
(SELECT Clientid FROM purchase WHERE purdate >= '12/31/2004' AND purdate <=
'1/1/2005' )

Access query
select * from Clients where clientid not in
(SELECT Clientid FROM purchase WHERE purdate >= #12/31/2004# AND purdate <=
#1/1/2005# )

I do not have purchase records between those dates, I get all clients in SQL
query but nothing in Access.

Sunny:

On Win2k SP-4, MS Access 2000 SP-3, JET 4.0 SP-8.

DDL:

CREATE TABLE Clients
(clientid LONG
,CONSTRAINT pk_Clients PRIMARY KEY (clientid)
)

CREATE TABLE purchase
(purchaseid AUTOINCREMENT
,clientid LONG
,purdate DATETIME
,CONSTRAINT pk_purchase PRIMARY KEY (purchaseid)
,CONSTRAINT fk_purchase_Clients
FOREIGN KEY (clientid)
REFERENCES Clients (clientid)
)

Sample Data:

Clients
1
2
3

purchase
1 12/29/2004
1 12/30/2004
2 12/30/2004
2 12/31/2004
2 01/01/2005
3 01/01/2005
3 01/02/2005

The original Access query:

select *
from Clients
where clientid not in
(SELECT Clientid
FROM purchase
WHERE purdate >= #12/31/2004#
AND purdate <= #1/1/2005# )

When I execute the above, the results:

clientid
1

This would be correct based on the sample data I created. Client 1 is
the only client that has no purchases between the dates noted.

A simpler version of the above would have been:

select *
from Clients
where clientid not in
(SELECT Clientid
FROM purchase
WHERE purdate
BETWEEN #12/31/2004# AND #1/1/2005# )


I'm not sure why your query is not working, becuase it is working for
me.


Sincerely,

Chris O.