Query based on created date

[JSimoncelli]

The Title is a little misleading because I didn’t know how to present it… I created a query based on a table that contains several fields. The fields that drive the query are a date field “PrevInvestDate” and type field “Eligibility”.. The objective was to create a “ReInvestDate” based on the “PrevInvestDate” and “Eligibility”. I created the following formula to create a date based the above factors:
ReInvestDate: IIf([Eligibility]="SCI",DateAdd("yyyy",+5,[PrevInvestDate]),IIf([Eligibility]="TS",DateAdd("yyyy",+5,[PrevInvestDate]),IIf([Eligibility]="S",DateAdd("yyyy",+10,[PrevInvestDate]))))
This works fine, the issue comes in when I try to add criteria of “Between [Start Date] And [End Date]”…

This may a case of the cart before the horse, not sure. I also tries creating a query that performs the date calculation and a second query with the other information (adding the first query to get the calculated date). I then tried the date range criteria and no go…
John....

 

[JSimoncelli]

A quick update: The second query works using the >#XX/XX/XXXX# and <#XX/XX/XXXX#... However using the Between [Start Date] and [End Date] does not work... To me this seems to be a case where the "Between" action may be trying to happen before the date calculation.... Don't know...

John

 

[namliam]

if your start date and end date are parameters (you enter them when you run the query), then this is your problem... because parameters entered like that are by default Text unless you forfully change them.

Typicaly I wouldnt do this with an Iif and definatly not in the way you are doing it.
What happens if you get other Eligibility types with 3 or 7 years? You add IIF after IIF after IIF instead making a seperate table that holds your "years" related to your Eligitility should make life a lot easier.

 

[JSimoncelli]

Thanks for your help on this..
I understand your comment about adding additional Iif statements, however the Ts, S and SCI are the only Eligibility types that are used for the calculation in the query and these won’t change…
Your suggestion of moving the “years” to another table sounds good, how would you structure that table?
The related information is: Eligibility Type, PrevInvestDate, and a number of years dependent on the Type (5 or 10 years) to produce a ReInvestDate….
Again Thanks
John

 

[namliam]

The related table would have 2 columns, Eligibility, Years

Your dateadd would then simply be:
DateAdd("yyyy",[Years],[PrevInvestDate])