Finding records between Dates

[pr2-eugin]

Hello,

I have a table tbl_PolicyDetails with details of the policy the customers have with us.. In the tbl_PolicyDetails, I have two fields (these are the ones in question) called

1. "policyStarted" - Start Date of a Policy and
2. "policyPeriod" - Term of Policy Monthly/Annually/Quarterly

So in a Form view I just get the Next installment for the customer based on this information for display.. For example..

• Mr Butters Stotch's policy started on 26/04/2013 and is paying Quarterly; the next (i.e. 2nd) installment would be on 26/07/2013..
• Ms Wendy Testaburger's policy started on 07/04/2013 and is paying Monthly; the next (i,e. 3rd) installment would be on 07/06/2013..

This is not hard to get.. I have that sorted..

But the problem is, my manager wants to be able to specify two dates and search all policies that will be 'paying in' that Date range should be picked up..

So if the search range happens to be.. 01/07/2013 and 31/07/2013 Then Mr Butters Stotch's policy should be picked up, as his next installment falls on 26/07/2013.. Since this information is not stored, I cannot run a direct Query on this.. Also as the Installment number varies for each customer, I would not be able to just add 1 - Month or Quarter or Annum, and see if the date falls in that range..

So what would the criteria be in this scenario.. Am not sure how to get this to work.. any help will be greatly appreciated..

 

[pr2-eugin]

Just to make this clear, I am not using an inbuilt Query, I am building a Dynamic Query by using QueryDef.. As this is just another search Criteria.. There are several search factors that needs to be placed..

 

[pr2-eugin]

Well easier that I thought it would be.. I just created a Function, passing the two field values as argument, calculated the Next installment and checked, if it falls between the Date Range, got my desired result..

Am one happy man now..