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?