Jump to content

  •  

UPGRADE DEADLINE - SEPTEMBER 26, 2014!
FileMaker Inc. has a deadline for users of version 10,11, 12 as Individual box or volume licenses (with expired maintenance).
If you don't renew your maintenance and upgrade to FMP 13 you will no longer be eligible to upgrade, at the discount pricing.

Volume Licensing upgrade pricing for FileMaker Pro 13, FileMaker Pro 13 Advanced and FileMaker Server 13 will be discontinued.
Individual upgrade pricing for FileMaker Pro 13 and FileMaker Pro 13 Advanced will increase after September 26, 2014.
As of 27-September-2014, FileMaker 10 products will no longer be available for purchase or support.

http://help.filemaker.com/app/answers/detail/a_id/13865


Photo

Greater than date in a IF statement trouble


  • Please log in to reply
8 replies to this topic

#1 RichGags  newbie

RichGags
  • Newbies
  • PipPip
  • 2 posts

Posted 08 March 2012 - 12:59 PM

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
  • 0

#2 Raybaudi  ChindoguMaker

Raybaudi
  • Members
  • 3,222 posts
  • LocationRome, Italy
  • FM Application:12 Advance
  • Platform:Windows XP
  • Skill Level:Intermediate
  • Time Online: 47d 10h 24m 33s

Posted 08 March 2012 - 01:17 PM

1/31/2012 isn't a date.

Try: Date Booked > Date ( 1 ; 31 ; 2012 )
  • 0
Daniele
--------------------------------
I'm sorry for my English

#3 RichGags  newbie

RichGags
  • Newbies
  • PipPip
  • 2 posts

Posted 08 March 2012 - 02:30 PM

Perfect. You're the best!
  • 0

#4 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,787 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 217d 23h 53m 28s

Posted 08 March 2012 - 04:21 PM

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!! :laugh2: This is absolutely the best forum for FileMaker.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#5 comment  consultant

comment
  • Members
  • 24,185 posts
  • Time Online: 328d 23h 39m 52s

Posted 08 March 2012 - 04:55 PM

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.
  • 0

#6 Vaughan  Mostly Harmless

Vaughan
  • Moderators
  • 10,294 posts
  • LocationSydney, Australia
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:8, 9, 10, 11
  • Membership:TechNet
  • Time Online: 4d 9h 48s

Posted 08 March 2012 - 09:13 PM

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.
  • 0
Vaughan Bromfield
Sydney, Australia

Please post questions to the Forum, not directly to me. Back-up your files before making changes!

Whenever I hear the term "popular culture" I reach for my Iridium Q-36 Space Modulator.

#7 comment  consultant

comment
  • Members
  • 24,185 posts
  • Time Online: 328d 23h 39m 52s

Posted 09 March 2012 - 02:46 AM

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?
  • 0

#8 Aussie John  wanna be master

Aussie John
  • Members
  • 597 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Time Online: 3d 4h 51m 11s

Posted 15 March 2012 - 10:10 PM

I think Vaughan is referring to australian date structure as being dd/mm/yy as distinct from the OPs mm/dd/yy
  • 0
cheers John
--
advanced 12 Mac OS 10.7

#9 comment  consultant

comment
  • Members
  • 24,185 posts
  • Time Online: 328d 23h 39m 52s

Posted 16 March 2012 - 03:02 AM

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.
  • 0




FMForum Advertisers