Set a limit to dateDiff

[Mafi]

I created a query with calculated field months:

DateDiff("m", 
    [dateacquired], 
    [Forms]![form2]![txtendingdate])+1

I also have a field lifeTime, what i want to do is to set a limit to months fields so that it will not be greater than lifeTime

[Nauticalgent]

Try using this as the fields control source:

=IIf(
    DateDiff("m", 
        [dateacquired], 
        [Forms]![form2]![txtendingdate]) + 1 <= [lifetime], 
    DateDiff("m", 
        [dateacquired], 
        [Forms]![form2]![txtendingdate]) + 1, 
    [lifetime])

Sloppy as hell, but it should work...

[Mafi]

Wow! It works perfectly thanks. What can i do to return negetive months to zero. Eg when i view previews years that is b4 dateacquired it gave me negetive months

[Nauticalgent]

Glad it worked. To set negative numbers to zero, you could nest the IIF statement. I have shown you a quick example, why not give it a try and if you cannot make it work, post back here with what you have tried so we can show you where you may be getting it wrong.

Not that I mind helping out, but I like to be taught to fish rather than having my fish given to me.

Deal?

[Nauticalgent]

This may give you some ideas:

http://www.simply-access.com/Nested_IIF_Statement.html

[Mafi]

I have gotten it. Thanks alot

[Nauticalgent]

Good deal. Care to share your solution so that others may learn from your blood, sweat and tears?