US timestamp field changes to UK timestamp format in calculated field (Filemaker 10)
I have a calculated field (DateRecNo) that I use to link my databases and tables, consisting of the DateCreated field (a timestamp) plus the serial number of the record (DateCreated & "-" & RecordNumber). I've bounced back and forth between system formats for the dates in the past, and now my problem is that DateRecNo stubbornly continues to display in the UK format, despite the fact that the Date Created field is in US format (and my system format is now set to the US format). This has not been a problem in the past 3 years-- but it's the beginning of the new year for this database and in the process of importing records into a new template, this problem has just surfaced. I'm using a Mac Mini with OS X and just updated my FM from 10.0 to 10.0.3.
So if DateCreated is: 8/26/2005 11:01:02 PM
DateRecNo is still showing up as: 26/08/2005 23:01:02-189
... and the link to tables that use the US date format doesn't work.
Things I've tried after looking around on the Forum:
1. Deleting and re-creating the DateRecNo field
2. Setting the System date/time format to South Africa (which uses year/month/day), and then back to US format
3. Writing a new calculated field as:
Month(Date created) & "/" &
Day(Date created) & "/" &
Year(Date created) & " " &
Timestamp(Date created;Date created) & "-" &
... but this shows up as 8/26/2005 26/08/2005 23:01:02-189 because the Timestamp function still gets the UK format
4. Setting a startup script to UseSystemFormats=On for this database. File Prefs are also set to "always use system settings".
5. Changing the storage options for DateRecNo to "do not store; recalculate as needed" (no matter how I set this up, it doesn't change the date format)
6. Saved the database in compacted format
7. Changing the DateRecNo formula to GetAsTimestamp(Date created) & "-" & RecordNumber
I'm out of ideas and getting frustrated. Why won't Filemaker use the correct date format in the calculated field?