5 Replies Latest reply on Nov 25, 2012 8:53 PM by philmodjunk

    I'm in a mess with dates



      I'm in a mess with dates



           I decided to do a Find of a range of dates, then create a Script for it. However, any way I tried to find dates in the range 1/2/2012...31/12/2015 either returned ALL records or NONE. Baffled, I could see nothing wrong, so in the end I decided to do a Sort by date. The result was somewhat zany! I got a 3-date sort - 1. from earliest to 2010  2. 2012  3. 2011. I couldn't fathom this at all, then looking closer, I realised what has happened...

           I have a database of dates in mixed US (grrrrr, "illogical, Jim") and UK formats. That throws Find and Sort into total confusion. I know why it happened, but that's a long story. What I want to know for now - how do I resolve it? Ideally I would like to have all dates in the perfectly logical UK & worldwide format (DD/MM/YYYY), but until that happy day I would like at least for Find and Sort to work properly so I can have a Script for all records modified on or after 1st February 2012.

           Any thoughts? (FMP10 running in 10.5.8 on a G4 PB).



        • 1. Re: I'm in a mess with dates

               Apparently this is a field of type text instead of date. Correct?

               How can you consistently tell that a date is in MMDDYYYY or DDMMYYYY format?

               01/02/2012 fits either format and can represent either January 2nd or February 1st.

          • 2. Re: I'm in a mess with dates

                 Hi Phil

                 No, this is a Date field, set up to automatically enter the Modification Date. (The database is quite old). The data is a mixture of US and Worldwide date styles (long story), which as you say can be hard to tell apart where the DD is l2 or less. However, where the DD is greater than 12 it is easy to spot.

                 The situation is now even worse. Yesterday I set up a new field Date of Recording, in the following format:


            1.           Date field
            3.           No Auto-enter values
            5.           Validate Only during data entry
            7.           Allow user to override during data entry


                 I successfully entered dates in the non-US format, e.g. 25/8/2010. However, if I tried to enter "May 2010" I got the usual FMP error "This must look like a date and be in the range... etc etc". This means that the user is NOT allowed to override during data entry!!

                 So I thought, "Oh what the h*ll" and converted the field to Text instead, so I could enter "May 2010" if I wanted. That seemed to go ok until I looked at the data I'd entered yesterday while it was still a Date fieldThis was all now in US format - so where I'd successfully entered 25/8/2010, the text now displayed as 08 25 2010. So I checked the International Date and Time formats in System Preferences, and it is all defined as United Kingdom. So there is no reason for it to change to US display after converting from Date to Text.

                 I thought this was all due to having had dates in both US and Worldwide formats in the past, and therefore this was partly my fault. However yesterday's experience with a new date field shows this isn't true - there's something wrong in FMP. I now have two problems:

            1.           A mix of US and Worldwide date formats that prevents Find and Sort working properly
            3.           Converting from Date to Text reverts all dates to display US-style, even if the International format is defined as UK
            • 3. Re: I'm in a mess with dates

                   This doesn't fully add up here.

                   Dates are stored internally as numbers--the number of days from 12/31/0000 to the date stored in the field. (I'm using MM/DD/YYYY format here). Thus, this value is fully independent of what system formats are in place at the time the date was entered. System format settings are only used by FileMaker to correctly interpret which number represents the day and which the month during data entry.

                   You can then use data formatting options selected inthe Inspector to display a date in any format desired, not only choosing between mmddyyyy and ddmmyyyy formats but whether to use 2 or 4 digits for the year, whether to include the month name, day of week name etc. Given the confusion in formats here, I'd select formatting that includes the name of the month in order to correctly interpret the dates.

                   In no system settings would "May 2010" be considered a valid date for data entry, nor is it valid when in find mode.

                   There can be issues with a FileMaker file when it is created with one set of system formats are in place and then you open it on a computer with different system formats or change your computer's system formats. The fix can be to save a clone of the file on the computer with the desired system formats and then to import your data into this clone.

              • 4. Re: I'm in a mess with dates

                     Time to give you the "long story" Phil. 

                     This - and several other FMP databases - was set up in FMP2 with System7 settings set to UK.

                     In 2006, I got an iMac G5 running Tiger, and upgraded from FMP4 to FMP6 as I was running OS X for the first time. All went smoothly, and I set SysPrefs to be UK date formats, but then tweaked it using Custom.. setting so that seconds were displayed in the time (but retained UK dates of course).

                     Soon after that I started getting messages when I launched a database for the first time, saying something like "This database does not appear to be using the System settings for dates - do you wish to use System Settings?" Well, of course I did! I had no idea why this message appeared, but thought it was something to do with a new computer, new OS, and new version of FMP.

                     Then I began to notice that all my dates started appearing in FMP in the US format. I checked in SysPrefs and was still set for UK time, so I couldn't understand this. It was quite some time before I discovered the ghastly truth. If you use a Custom.. setting for Date & Time, the date is held - in the background - in US format, even if that's not what you see. So in effect, and without ever intending to, I DID change the System Settings. This is not the fault of FMP : they did issue a warning message after all : it's thefault of Apple for holding the date in US format even when you're using a different custom setting.

                     The problem with your solution, is that now Leopard can display seconds without a custom setting, I've ended up with files that have used two different date formats (UK > US > UK), and have dates being displayed in a mix of the two formats. Are you saying I've no chance of resolving this?  

                • 5. Re: I'm in a mess with dates

                       Date Display and Date Storage are two different things. You have full control over how a date is displayed via the data tab in the inspector.

                       What I have suggested is two steps totally in FileMaker:

                       Use save a copy to save a clone of the file.

                       Use Import records to import data from the original file into the clone.

                       I do not know if this will fix the issue but it may be worth trying as it is a fairly simple thing to try.