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.
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.