2 Replies Latest reply on Jul 6, 2011 11:25 AM by fredmckinnon

    Problem with Date Formats - Easy Fix?

    fredmckinnon
    fredmckinnon Jul 6, 2011 6:16 AM

      Title

      Problem with Date Formats - Easy Fix?

      Post

      Hey Gang,

      I've got a problem with my "Date" field.  It's set to auto-enter the create date.  The current format of the field is:

      Field Type:  Date
      Filed Options:  Auto-enter "Creation" Date
      In Layout mode, in the inspector, the "date" field is set with:

      Data formatting:  Date
      12/25/2003

      However, for some reason, there continues to be entries showing up (either from previous imports, or erroneous entry, not sure), that will show up as a "?" in my view.  When I click in the field where the ? is ... the date format will be like:

      19-Apr-11

      I can simply manually change it to 4/19/11 ... but this is taking forever, I have tens of thousands of entries that have a ? ... and they don't even sort properly ... if I sort by date, they are all scattered around.

      Is there a simply fix to find all these dates in that dd-month-yy format and have them corrected automatically w/ a mass format?  I thought that by selecting the data format in the layout menu this would do it, but no such luck.

      • 108 Views
      • Tags:
        • 1. Re: Problem with Date Formats - Easy Fix?
          philmodjunk
          philmodjunk Jul 6, 2011 10:06 AM (in response to fredmckinnon)

          It would seem that you have text imported into this field from another source. Using text for the month name isn't a valid format for entering a date in FileMaker so you get the question mark.

          Try this experiment on a copy of your file to see if it works:

          enter find mode and put a ? in this field, then perform a find. This should find all records with invalid dates in this field.

          Try this expression in a Replace Field Contents operation with the calculation option to see if it works:

          Let ( [ D = YourTable::YourDateField ; Day = Left ( D ; 2 ) ; Month = Middle ( D ; 4 ; 3 ) ; Year = 2000 + Right ( D ; 2 ) ] ;
                    Date ( Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Month ) / 3 ) ; Day ; Year )
                )

          I am assuming that all the invalid dates follow the same format, which may not be the case here. I'm also hoping that YourTable::YourDateField will return the actual text imported, not the ?. If it doesn't, you might try getastext ( YourTable::YourDateField) to see if that works.

          • 2. Re: Problem with Date Formats - Easy Fix?
            fredmckinnon
            fredmckinnon Jul 6, 2011 11:25 AM (in response to fredmckinnon)

            Phil,

            Thanks for the reply.  (I'm guessing you saw our mess I sent a while back and figured you'd stay far away from our templates haha!) ... I did the search for ? and it returned 40K+ of 54K records.  So yes, it seems the majority of our entries are screwed up.  IT's all from when we imported everhything from DabbleDb into FileMaker a few months ago - we had nearly 2 years worth of DabbleDB entries, and the import into FileMaker didn't go as well as I'd hoped - most likely due to user-incompetence!

            A while back you'd given me a way to calculate a date field by stripping the timestamp stuff, and I forgot I even had that column at all!  I can use it for the summary reports.

            Still stumped on some of the weekly summary reports that are condensed, if you ever find time ... you should still have the files I sent.