Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
Jabert

Handling date searches in leap years

Recommended Posts

Jabert

Looking for some tips on how to handle leap year dates. I have a file that needs to search for monthly data (i.e. 1/1...1/31, etc.). This all works fine, except for leap years, where 2/1...2/28 leaves out the 29th. Can anyone suggest some solutions to this?

 

TIA

Share this post


Link to post
Share on other sites
Steve Martino

I don't quite know exactly how you are doing these searches, but you can use a variation of this calculation:

 

Date(Month(TABLE::dateField);1;Year(TABLE::dateField)) & "..." & Date(Month(TABLE::dateField)+1;0;Year(TABLE::dateField))

Share this post


Link to post
Share on other sites
Jabert

Thanks, but I think you are too far ahead of me. The search I am using is simply using a first day, last day criteria. Like this: Find (1/1/17...1/31/17). This finds all the data for the period defined, but when I get to a leap year I have a problem. I guess I could put in some kind of field that detects when the year ends in 00, 04, 08 ,...96, and somehow change the February search to 2/1/20...2/29/20. But that seems awfully clunky and prone to problems. I'm not greatly experienced with FMP, so a less complicated version than the one you suggest is what I'm after.

Share this post


Link to post
Share on other sites
doughemi

Let FileMaker do the heavy lifting, rather than kludging something together that may not work in all cases.

 

Steve's calculation takes advantage of the fact that FM considers the 0th day of the month to actually be the last day of the previous month. (But you can't manually enter 3/0/2020). The attached example shows a way to script the find so that the user doesn't have to worry about it (or understand FM's search range convention).

 

Create a global g.searchMonth field, and a global g.searchYear field. Add a Search button that runs the following script

 

Enter Find Mode []
Set Field [jabert::date[]; Let(
[~startDate = Date(jabert::g.searchMonth; 1; jabert::g.searchYear);
~endDate= Date(jabert::g.searchMonth + 1; 0; jabert::g.searchYear)
];
~startDate & "..." & ~endDate
)]
#
Perform Find []

 

Sorry, you won't be able to use the sample. I'll make a .fp7 version and add it later this evening.

jabert.fmp12.zip

Edited by doughemi

Share this post


Link to post
Share on other sites
Mark Pelleymounter

If you want to seach a date range that is actually the entire month, you do not need to enter it the way you are doing ("01/01/2017...31/01/2017" (uk date format), you can just enter "01/2017" and that does the entire month. Likewise if it is February, then enter "02/2016" will find all dates in Feb 2016 (Which would include 29th Feb 2016).

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use