Checking all fields associated with an ID against a date

[cr84net2]

I have a table named coupon with ID, bisid, coupstart, coupexp, etc.

ID--bisid---coupstart---coupexp
1----2-------05/15/05---05/20/05
2----6-------06/12/05---07/23/05
3----12------06/13/05---07/30/05
4----2-------05/20/05---06/22/05
5----6-------05/21/05---06/02/05
6----2-------05/30/05---06/22/05
7----12------05/30/05---06/25/05

I need to get the distinct bisid of the records that do not have a current coupexp date. So in the table above only bisid 2 would qualify because all of the coupexp dates have passed. The other 2 bisid's have at least one current coupexp date.

To make this more convoluted I have another table called busreg which has ID, bisname, etc. ID from this table and bisid from the coupon table match so I will need the bisname that matches the ID which matches the bisid from the table above.

Here is the select statement I started with:

needcoup= Date

strQuery= "SELECT bisid, coupexp, busreg.ID, bisname FROM coupon, busreg WHERE bisid=busreg.ID AND coupexp< #" & needcoup & "#"

This obviously won't work. Any help would be appreciated!

 

[DotNetGnat]

i think you would get a better and quicker response if you post this question on SQL Server Programming forums.

thanks

-DNG