Derive date range based on Expression

[JSimoncelli]

I have a query that I want to “filter” based on a date derived from a Build statement (Expression). The Build statement works I can even sort by “Ascending”.. But when I try to add a “Between [Startdate] And [Enddate] statement in the Criteria box, returns nothing….
Build statement : ReInvestDate: IIf([Eligibility]="SCI",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="TS",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="S",DateAdd("d",+3650,[PrevInvestDate]))))

Thanks
John

 

[JSimoncelli]

I guess the real question is; can you derive a date range where the date only exists in the query?
If I can sort by this date than logic dictates I should be able to apply the “Between [Startdate] And [Enddate]” to the Criteria box.
Of course logic may have nothing to do with it.

John

 

[jdraw]

Can you describe WHAT you are trying to do in plain English?

No dateadd, no Between, no IIF and no filter.

Just a plain old

I have this and this, I want to find out....
and then ....

use some numbers and an example if necessary.

-If you can't describe it, who is going to build it???--

 

[JSimoncelli]

Sorry, here is the skinny of what I am trying to do….

Referencing the attachment, attempting to produce a report based on this query. The report lists employees based on their next investigation date.

This date is affected by two factors one causing a result 5 years and 10 years in the future respectively, each employee will only be affected by one or the other. The calculation of this date is made in the “ReInvestDate” statement.

Bottom Line: Report listing employees with their previous investigation date their next investigation date within a give date range.

John

Thanks in advance

 

[JSimoncelli]

I may have found the answer, if the result of the expression returns the date as text I believe that will cause this issue….
If so can I fix this?
John

 

[jdraw]

see if this helps.
http://www.techonthenet.com/access/functions/datatype/cdate.php

 

[JSimoncelli]

I see how this works however I don’t know if I can fit it into my expression:

ReInvestDate: IIf([Eligibility]="SCI",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="TS",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="S",DateAdd("d",+3650,[PrevInvestDate]))))

I tried this:

ReInvestDate: CDate(IIf([Eligibility]="SCI",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="TS",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="S",DateAdd("d",+3650,[PrevInvestDate])))))

Didn’t work, might be too much for the expression, and may have to go with VBA code…

Not sure if the text vs date is the answer, just a shot...

John

 

[JSimoncelli]

My VBA code skills are not that good..

John

 

[jdraw]

Can you do a compact and repair and make a sample copy of your database --remove anything confidential, then zip the file.

Also, include specific instructions as to where the issue is etc.

Good luck.