1 Reply Latest reply on May 25, 2012 12:31 AM by Sorbsbuster

    Date format mismatch when comparing two dates?

    PeterThorton

      Title

      Date format mismatch when comparing two dates?

      Post

      Hello,

      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:

      25.12.2003

      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?

      filemaker_date_blues.png

        • 1. Re: Date format mismatch when comparing two dates?
          Sorbsbuster

          The problem is you are trying to make the search criterion a comparison, like "if this field is greater than this other field".  You can't specify search criteria like that.

          You would do it by going to the first field and collecting its value as a $Variable, then enter find mode, then use Set Field to set the field you want to search on to have the $Variable value.  Then Perform Find.