Finding latest Ending Date for a 3combo group 1

[molly]

I have a qry that shows List Prices for my company. I want to find the present list price for the cust/product/pricelevel. I am hoping to have a criteria line in my Qry.

i keep the old and new List prices in the same table. I use a startdate and enddate for the effectivity of the List price. For example, for one customer's product and his 2 price levels, you will see his pricing history in a subform:

custID prodID level price Startdate EndDate
1234 5 1 23.00 1/1/2007 12/31/2007
1234 5 2 19.85 1/1/2007 9/31/2007

1234 5 1 21.00 1/1/2006 12/31/2006
1234 5 2 18.00 1/1/2006 12/31/2006
1234 5 1 17.00 5/1/2005 12/31/2005

i would like another Qry to just show the records which have most recent enddates per 3combo cust/product/level relative to today's date.

Here it is 10/13/2007. i do not want the 9/31/2007 second line because the effective date has expired compared to today's date. That price level 2 is not offered after 9/30/2007. So really, i just want the Qry to show the 1 example $23 record above. The rest of the history price records would not show in this Qry.

Can I stay within an Access qry criteria line for this?

 

[PHV]

WHERE Date() Between Startdate And EndDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[molly]

I have this working now. I put into the Qry criteria line as follows:

StartDate field:
<=Date()

EndDate field:
>=Date()


Molly