Searching date before and using this in WHERE condition

[mor]

Hi everyone,

I have a table that has 4 fields; id_vl, id_product, date_vl, vl.

Basically each product is valued either on a monthly, weekly or daily basis. I want to create a filter that is based on a text box that filters the dates from which one can look at the dates of valuation and the associated values.

The problem comes with the products valued on a weekly or monthly basis. For example, if I simply say [tblvl].[date_vl]>=Me.textbox, and me.textbox = 13/09/2013, yet the last value date for a weekly product was 09/09/2013, this record won't be included. Therefore I need to set the date of the previous record as the filter. This is my attempt but it doesnt work...

SELECT tblVL.Id_VL, tblVL.Id_Product, tblVL.Date_VL, tblVL.VL,

FROM tblVL
WHERE iif(tblvl.date_vl<>me.fees_start_date, [tblVL].Date_VL>=(SELECT 
             MAX(PrevDate.Date_VL) 
     FROM tblVL AS PrevDate WHERE PrevDate.Date_VL<[tblVL].[Date_VL] AND PrevDate.Id_Product=tblVL.Id_Product;),tblvl.date_vl>=Me.fees_start_date)
 ;

Can anyone help?

 

[Brianwarnock]

If I understand you correctly you want all records for a Procter after a given date but if because of the review period there are non then you want the last record.

I am a simple guy who likes simple solutions so I would use two queries.

Query1
Groupby id_product and max of date_vl as prevdate

In query2 join this to the table on id_product and criteria

Where date_vl >= me.fees_start_date or prevdate < me.fees_start_date

Brian