How do I find Records with Dates This or Last or next Month in FileMaker Pro 10?
Thank you for your post.
When searching for a specific date range (for example, this month), you could enter Find Mode and enter:
This will find all records in the range 1/1/2009 to 1/31/2009.
Since you are finding for an entire month, this can be simplified to:
Now, we can put this into a script to make this automatic, so you don't have to enter the month. That is,
Enter Find Mode 
Set Field [ <date field> ; "1/2009" ]
Perform Find 
However, this is static and will not change when we enter February. Therefore, we can store the month number to a variable and then use the variable to find. Therefore, the script to find records for this month would be:
Set Variable [ $var ; Month (Get (CurrentDate)) & "/" & Year (Get (CurrentDate)) ]
Set Field [ <date field> ; $var ]
Finding the previous month and next month can be a little tricky. For the next month, it would seem logical to add 1 (one) to the month, but if you are in December and add one, you get 13, which will not work. Likewise, for the previous month, subtracting one from January leaves you at 0.
Here is the script for next month, and I'll discuss the calculation afterwards.
Set Variable [ $var ; (Mod (Month (Get (CurrentDate)); 12) + 1) & "/" & (Year (Get (CurrentDate)) +
If (Month (Get (CurrentDate)) = 12; 1; 0 )) ]
Set Variable [ $var ; Date (Month (Get (CurrentDate)) - 1; 1; Year (Get (CurrentDate) ) ) & "..." &
Date (Month (Get (CurrentDate) ); 1; Year (Get (CurrentDate) ) ) - 1) ]
Thank you, but unfortunaltely none of this works.
It will only show records for January with "**/01/2009", while it doesn't work with "1/2009".
The script doesn't work, could it be because I have a different language version of the software or does the language only apply to the layout (menus, etc.?)
Ehm...I've realized just now that the date field I'm trying to search wasn't formatted as a date, but as text. Now I've changed it, and I figured out how the script instructions translate in my language, and it works perfectly. Just what I needed for my reports. Thank you!
Retrieving data ...