AnsweredAssumed Answered

Date format mismatch when comparing two dates?

Question asked by PeterThorton on May 24, 2012
Latest reply on May 25, 2012 by Sorbsbuster


Date format mismatch when comparing two dates?



I'm trying to make a "monthly statement" report, where I let the user pick two dates, and the report will list all the orders that were made between those dates. I have field OrderDate, which is a Date type, and I'm trying to compare it with another Date field in another table, in a Perform Find script step. The result, as you can see in the screenshot, is a messagebox with the following text:

The value of this field must be a valid date in the range of years 1 to 4000 and should look like "25/12/2003".

The message shows up when I hit the Add button, and no matter which option I click, the condition won't be added to the list.

the day/month/year format might seem strange to you, but it's just the European format. I'm currently residing in Japan (where, incidentally, the format is year/month/day) but I'm form a country in Central Europe, and my PC's date format is still set to that country.

I looked at the actual values in the OrderDate field, they are in the following format:


which is the standard date format in my country. When I try to change the dates to the aforementioned format, "25/12/2003" (or any other date format I can think of, like "25-12-2003" or "03-12-25"), FileMaker just silently converts it back to "25.12.2003" when I'm done editing. Funny thing is, when I try to change the value to something that's not a date (I just bash the keyboard to get a random string of letters) FileMaker presents me with the exact same message it showed me when I tried to add my search criteria. If I click Revert field, FileMaker reverts to the original value, but if I click Ok, it will allow me to input nonsense in the field.

What can I do? Which settings to I need to change to make this work?