Greater than date in a IF statement trouble

[RichGags]

Hi. I am trying to create an IF statement to determine if the Date Booked for an event is greater than 1/31/2012 AND if the record is tagged a certain company.

Right now I have...

If [services::Date Booked > 1/31/2012 and Event::INC_LLC = "LLC"]

Problem is, if it is an LLC event, it always validates true regardless of what the date is. What am I missing? Thanks, Rich

[Raybaudi]

1/31/2012 isn't a date.

Try: Date Booked > Date ( 1 ; 31 ; 2012 )

[RichGags]

Perfect. You're the best!

[LaRetta]

If [services::Date Booked > 1/31/2012 ...

it always validates true regardless of what the date is. What am I missing?

Hi Rich,

To explain why the evaluation is breaking, FM sees 1/31/2012 as '1 divided by 31 divided by 2012'. The result is: .0000160328352466. So any Date Booked would evaluate as greater (except if empty). When working with dates, using date functions protects from this.

And welcome to FM Forums!! This is absolutely the best forum for FileMaker.

[comment]

an event is greater than 1/31/2012

Not directly related to your question, but in general it's best to avoid coding data directly into a calculation.

[Vaughan]

Indeed, if I ran that calculation it'd fail (or produce very unexpected results) because that date is the 1 day of the 31st month in 2012.

[comment]

Indeed, if I ran that calculation it'd fail (or produce very unexpected results) because that date is the 1 day of the 31st month in 2012.

Which calculation do you mean?

[Aussie John]

I think Vaughan is referring to australian date structure as being dd/mm/yy as distinct from the OPs mm/dd/yy

[comment]

I think so too - but the first calculation performs a division and the second one uses the Date() function. The results would be the same no matter which date format is in use.