Date Calculation Using Case Function and Greater Than Not Working

[tforbes]

I need to calulate the mileage costs based on various rates within ranges of dates.

For example, if the date of travel falls between July 1, 2012 and December 31, 2012 the travel cost value = .555, and if the travel date falls between January 1, 2013 and June 30, 2013 the travel cost value = .565

I have tried the following case function for the field <<mileage_cost>> with 2 records that contain the dates "12/12/2012" and 1/31/2013" respectively. The function returns the value ".555" for both cases, but should return ".565" for the second case.

Case(

travel_date > 6/30/2012; .555;

travel_date > 12/31/2012; .565

)

[bruceR]

12/31/2012 is 12 divided by 31 then divided by 2012

 

The result is .000192394022959

 

You need to use the date function. 

Also, make sure your field travel_date is actually a date field.

 

Case(
travel_date > date( 6; 30;2012); .555;
travel_date > date(12;31;2012) ; .565
)

[comment]

Actually, what you should do is avoid hard-coding data altogether. Instead, define a table of travel rates and lookup the appropriate mileage cost from there, using a relationship matching on date.