Unexpected date output of Select query

[liliya13]

Hi,

I have a table and I need a query with records where (stopdate-startdtSept1)>=90 or stopdate empty. All records under StartdtSept1 field have the same value 9/1/2003 .

I did the following query:
SELECT *
FROM MYtable
WHERE Datediff("d",[mytable].[stopdate],[mytable].[startdtSept1])>=90 or mytable].[stopdate] is NULL;

However I received unexpected output because some values under STOPDATE are 5/30/2002 ...which is BEFORE 9/1/2002 while I expected stopdate later than 9/1/2002 or empty

Could you please tell me what I am doing wrong?

Thank you in advance...

 

[PHV]

And this ?
WHERE (stopdate-startdtSept1)>=90 Or stopdate Is Null;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

what are the datatypes of the columns?

can you give a few sample rows?

DateDiff(xx,d1,d2) function expects the earlier date first, i.e. d1 < d2

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[liliya13]

Thank you! When I swapped d1 (earlier date) and d2 it started to work properly!

 

[liliya13]

To continue my previous question...

"DateDiff(xx,d1,d2) function expects the earlier date first, i.e. d1 < d2 "

Now I need to set a condition where value for DATE OF TREATMENT should be witween 9/1/2003 and 3/31/2004

Considering statement above...Would it be correct to do the following steps?

1st step
put >=9/1/2003 under the DATE OF TREATMENT in the Design mode grid and run query

2nd step
based on the above query put <=3/31/2004 under the DATE OF TREATMENT in the Design mode grid and run query

Won't it contradict the statement above?

Thank you!

Liliya

 

[PHV]

In the criteria cell of DATE OF TREATMENT:
Between #9/1/2003# And #3/31/2004#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244