Question Is it possible to make an expression based on another expression?

[fransanchezoria]

Hi,

I have a Datediff field that calculates the diffrence in months between two dates. Now depending in this value, I want to assign a number. For example, if the range for difference in months is (stored in a table):

Diff Value
<12 1
12-36 2
>36 3

I would like to build a query that could do the following:

Start date End date Diff Value

01/01/2011 01/03/2011 2 1
01/05/2010 01/05/2011 12 2
...

And so on. The field Diff is an expression, the problem is that I don't know how to do the Vlookup for the field Value when it is based in the field Diff, which is an expression.

Your help is appreciated

Fran

 

[Mr. B]

First, the calculated value of the number of months difference between your two dates should not be stored or saved at all. This should be something that you calculate everytime you need to know the value because the result of this calculation will continuously change.

You can still get the result you want by using the following as another field in a query:

Results: IIf(DateDiff("m",[startdate],[enddate])<12,1,IIf(DateDiff("m",[startdate],[enddate])>=12 And DateDiff("m",[startdate],[enddate])<=36,2,3))

If you want to just see the number of months so you can validate the results values in the field created above you can also have a column in your query using:

Months: DateDiff("m",[startdate],[enddate])