calculation between two dates

[Ben Ball]

I am trying to calculate a value as an "If" function but I can't seem to get it to work...

I am trying to create a calculation that will take in consideration the following.

If a "date_field" is between dates1 and dates 2, calculate result,

If a "date_field" is between Dates3 and Dates 4, calculate result.

I cannot seem to get this 2 work... I can view records in table based on a date from and to but cannot work out the calculation for this result..

TIA

[jstaphse]

Does this work?

Case (Date_Field ≤ Date1 and Date_Field  ≥  Date2;result1;Date_Field ≤ Date3 and Date_Field  ≥  Date4;result2;default result)

[LaRetta]

Does this work?

Case (Date_Field ≤ Date1 and Date_Field ≥ Date2 ; result1 ; Date_Field ≤ Date3 and Date_Field ≥ Date4 ; result2 ; default result)

I don't believe that fits the criteria of 'between'.

[color:gray]Date_Field ≤ Date1 and Date_Field ≥ Date2

To get between (inclusive), I would suggest reversing, as: Date_Field [color:red]≥ Date1 and Date_Field [color:red]≤ Date2 ; result1. I assume that's what you meant but I wanted to clarify. :wink2:

[Ben Ball]

Thanks jstaphse, and yes to clarify, and I always put these wrong way round first of all...

LaRettas adjustment is correct...

This works perfectly, thankyou.

[jstaphse]

Thanks for correcting that LaRetta. I knew it was too early for that reply. ???