Question Mark (?) in Date field

[Chemadian]

Hey Everyone, I've run into some formatting issues with a date fields i have. I opened up the file this morning and i discovered that my "expiry" field has a question mark instead of the date, i haven't changed anything with the layout and the field is definitely wide/tall enough so i don't really know whats causing it. When viewing the records the field displays the question mark, but when i click into the field in browse mode the question mark disappears and the date is displayed... This particular field does have custom formatting to show mm/dd/yyyy but the question mark doesn't go away even when i change the date format back to the default. I have also tried deleting the field on the layout and creating a new one but that doesn't seem to help either. Finally i messed around with my computers date preferences because i heard they might affect the Date fields in FM but it didn't seem to work either. has anyone run into this before??

[Vaughan]

It could be indexing or other corruption. Backup the file, then save a compressed copy and test the copy.

[bcooney]

What is the definition of your "expiry" date field? Sounds as if this is a calc. Confirm its result is set to "Date." You mention custom formatting. Please describe that too.

[Chemadian]

Vaughan,

I already tried killing the index both by saving the file as a compressed copy and also by removing the index option for the serial number and the problematic date field and then reinstating the index for both fields. Unfortunately neither of these methods worked.

bcooney,

The expiry date is not a calculation, it is entered by a drop-down calendar option. The custom formatting is just what i described in my first post, being that instead of yyyy-mm-dd i changed it to display mm/dd/yyyy.

Since my first post i have also created a new layout based on the same table and included all the fields and the problematic expiry field still displays a question mark. I have also tried expanding the expiry field waaay out to the right just in case the date being stored was some crazy date format such as " Thursday July Twenty-first, 2011" for example. Also didn't help. Finally i exported the records into excel to see how the expiry date field would be exported and it came across like it was supposed to, displaying the correct date. This export result was the same whether the "use current layout format" checkbox was checked or unchecked. To me it seems like the display format might be corrupt? not exactly sure what i can do about this but i'm pretty positive it won't be pretty. :mellow2:

[comment]

The question mark indicates an invalid date entry. When you click into the field (or format the field to display "As entered") you see the actual contents of the field. Filemaker is quite happy to store any kind of garbage in a date field - but when you ask it to format it as date, it needs to interpret the entry as a valid date.

It seems unlikely that your dates would decide to become invalid one fine morning. Did you import them?

[Chemadian]

Comment,

I originally did import them from a excel sheet, and looking back at the excel file the date format was in mm/dd/yyyy. When the file was imported filemaker changed the date to its preference (yyyy-mm-dd or whatever). I don't like this formatting so i custom formatted the field back to mm/dd/yyyy. That being said, i would like to think that if after this custom formatting was applied and the date field suddenly started displaying question marks it would have raised a flag to me that something wasn't right!

BUT, then again stranger things have gone unnoticed

If this is the problem, what is the best way to test/fix it?

[bcooney]

Good call on the import, comment. Even after reading, "it is entered by a drop-down calendar option."

[comment]

When the file was imported filemaker changed the date to its preference (yyyy-mm-dd or whatever).

Filemaker does not change imported data. You must understand that the way you format a date field affects only the display. There is a data entry setting that dictates how dates must be entered (including import). This is the short date format of the OS - either the one under which the file was created, or the current one (you get to choose which in File Options). You cannot import dates in mm/dd/yyyy format if the file is not using mm/dd/yyyy as its data entry setting.

[Chemadian]

Thanks for the clarification! So i checked my OS date preferences and changed the short date format to mm/dd/yyyy, In Filemaker file options, the "use current system settings". I created a new record and checked the data formatting option for the "expiry" field and sure enough the date option is now set to mm/dd/yyyy (i no longer have to specify a custom date option).

Now that i understand the background, how do i go about fixing this for the records previously imported with the invalid date format?

[comment]

how do i go about fixing this for the records previously imported with the invalid date format?

Are they YYYY/MM/DD?

[Chemadian]

no the excel records are mm/dd/yyyy. So they are the same format as the current OS settings.

I just switched the date formatting to "as entered" and it fixed it. Thanks for all of your help!

[comment]

I just switched the date formatting to "as entered" and it fixed it.

I wouldn't be sure about that. You still may have invalid dates - showing them "as entered" only hides the problem. Invalid dates will not sort correctly, and cannot be used for calculations.

---

Not critical, but recommended: once everything is all right, clone the file and import your data into the clone. The "Use current system settings" option has a few quirks.

Edited July 22, 2011 by comment

[Chemadian]

Ya in the back of my mind i thought that might have been too convenient! So clone an empty file and import all of the data from the original file into the clone and as long as the OS preferences are correct and the "use current system settings" is checked that should get rid of any invalid dates? Just want to make sure i do this correctly, so this problem won't re-occur in the future!

[comment]

So clone an empty file and import all of the data from the original file into the clone and as long as the OS preferences are correct and the "use current system settings" is checked that should get rid of any invalid dates?

No. This procedure is recommended at the end - AFTER you have fixed your invalid dates. Unfortunately, I don't know what makes your dates invalid (if they are still invalid). What was your file's date setting before you changed it to MM/DD/YYYY, i.e. when you imported the Excel document? Did you enter any other dates beside the imported ones?

[Chemadian]

I don't recall what the file's date settings were before, to be honest i didn't give date settings a second thought when i imported that excel file because as far i remember the date fields displayed properly after they were imported. Since importing the excel file i have entered multiple records, which display the correct date. The products that were imported from the excel file will revert back to the question mark if the date format is changed from "as entered" to "mm/dd/yyyy" (or any other date format for that matter), so you are correct in saying that the dates are still invalid. So now that my file settings are correct and the OS system is set to mm/dd/yyyy is there are better way than going through each record individually and "copy and pasting" the date back into the field?

[comment]

Try finding the invalid dates first (enter a question mark as the find criteria), then replace the Expiry field contents with a calculation =

GetAsDate ( Expiry )

Make sure you have a backup before you try this. Check the results very carefully, because a date can be valid, but still incorrect - such as 1/2/2011 that was originally February 1.