Query Filter

[chris_jolly]

Hey All,

I have asked previous questions and searched extensively on this topic and I am still without a solution.

Im trying to filter a range in a queries field. Here is the way my query is organized.

All this data deals with forecasting for my company...

Version - The current version of the forecast (represents a 2 year period techniqually)
Plant - One of our cement plants
CK Type - Clinker Type, main ingredant for cement

All of these are for place keeping, it is the way they need to be inputed into another tool.


Layout:
Version / Plant / CK Type / *Version (current) / *Version (next) / *Verison(after next) / etc.

* - Actual numbers eg 200909 for september, 2009

The other verison fields are for the actual data entry through a keystroke. They continue out to 2014 for the time being.

What I need to do is dynamically filter a range starting with the current version and ending with the version 2 years out. So I would be filtering the field.

Is this possible? Everything I have tried leads me to believe that it is not. Perhaps its in my design?

I know a formula that will get me the proper output for the version:

Version = 200711,200712,200801, etc.

iif(Version(right,2)=12,Version+89,Version+1) , but I cannot get this to populate the fields.

This is all I need to complete my database.

Thanks

 

[Norstar2]

iif(Version(right,2)=12,Version+89,Version+1) , but I cannot get this to populate the fields.

Not sure if you typed this right, but this line should be:

IIF(Right(Version,2)=12, Version +89, Version+1)

 

[chris_jolly]

Regardless of how that formula is written, I need to figure out how to apply it to the field in the query.

When I use the formula in the field it shows up as Expr 1, and the result from the formula is put into the bucket below it.


I really do need some help here, is there anyone out that can help??