Date formats driving me crazy!!! HELP...

[ffarmer]

I use the date format yyyy.mm.dd for all date fields (in all my data applications). My system date is set to that format. I've defined all my layout date fields as that format and have checked the content of my tables to ensure that dates appear as yyyy.mm.dd.

Yet, whenever I run a script to produce a report (layout), with a find field and a popup menu to select a range of dates (e.g. 2005.01.01...2005.12.31 or 2004.01.01...2004.12.31, and so on) I always get the message "Value of the field my be a valid date in the range of years from 1 to 4000 and should look like 12/25/2003". And my only choices are "OK" (which gets me no where) or "Revert" which ignores my date selection and produces a report with all records regardless of date. Useless.

I've tried everything to correct this, including adding a script which executes at file open, to Set Use System Formats [on]. Made no difference. I've checked and double-checked the system format (in System Prefs) and every date field in every table, in every layout..... Problem won't go away.

Why is FileMaker so insistent on the m/d/yyyy format??? I know it's an American standard, but yyyy.mm.dd is also widely used in the world and is a more logical format because of its hierarchical structure year.month.day. Better for sorting on date too!

How do I get this to work?_______

Thanks

[Genx]

... it also accepts dd.mm.yyyy ... maybe it just doesnt like year at the end?... anyway... double check that under the format menu you've got use system formats checked.

anyway if that doesnt work and your extremely desperate... you could just add an extra calc field... or auto enter text

year (date) & "." & month (date) & "." & day (date) ...

genx

[Lee Smith]

If I'm understanding you correctly, this is a Date Formatted Field.

If so, you can display a date field in a lot of ways (such as you have here), but it is being stored as a number,

In order to perform a find on it, you need to use the date format that is the default for the operating system (could be different than yours) the file was created on machine or country. In other words,

mm/dd/yyyy (range of mm/dd/yyyy,,,mm/dd/yyyy), or

dd/mm/yyyyy (range of dd/mm/yyyy...dd/mm/yyyy) etc.

HTH

Lee

[ffarmer]

My system date is formatted as yyyy.mm.dd

In Filemaker Pro 7 (which I'm using) the 'Use System Formats' under the Format Menu is check-marked.

The database has about a dozen tables, half of which have date fields in them. There are about 30 layouts (for reports and data-entry screens) and I've checked all the layouts with date fields to ensure they conform to the yyyy.mm.dd format.

But whether I try data entry, or run a report (with date range selection), I keep getting that damned message "....must be valid date .... and should look like 12/25/2003". Grrrrrrrrrrrrrrrrr.

I'm tearing my hair out (what's left!).

This database used to work fine. I've moved it to another computer (also a Mac, with OS X 10.4.4) with the correct settings. But I can't seem to convince FMP7 that I want yyyy.mm.dd formatted dates throughout!

I wish Filemaker preferences had a place where one could declare a default date format to be used throughout the FM database. I've spent countless hours trying to get this to work. Totally unproductive!

Any more advice?!

Thanks ---

[comment]

I believe cloning the file under your current system and importing the data into the clone should solve this.

[ffarmer]

[color:blue]PROBLEM SOLVED!

To get FileMaker Pro to accept dates formatted as yyyy.mm.dd (or any other custom format) for all layouts, including data entry and reports....

1) In the Mac OS X System Preferences, select 'International'. On the International panel, select 'Formats'. For Region, select 'Custom', then click 'Customize...' to modify the date formats. For the Short date format, arrange the year, month, day fields so they are year.month.day. Place periods as separators between year & month, and month & day. Remove any other separators (e.g. /) if any. Ensure year is four digits (click down arrow in year field), and that month and day are both two digits. Click OK.

2) Back on the International panel, select 'Input Menu'. At the bottom of this new panel, un-select 'Show input menu in menu bar'.

3) In your Filemaker layouts, format all date fields as yyyy.mm.dd (or yyyy.mm if you don't want to display day). If it appears under the Format menu, select 'Use System Formats'.

That's it!

So, what was I doing wrong?....

In the Mac OS System Preferences, I had selected Canada (as well as United States) as the Region. Then I selected Custom, thinking I could change the preset date formats for Canada or the U.S. Not so. Custom is like a separate Region, it seems. Then, on the Input Menu panel (in System Prefs) I had selected (check-marked) 'Show input menu in menu bar'. This causes a Canadian or U.S. flag to appear in the right-side of the Finder menu bar. This seems to have been my big mistake. Once I unselected this option (and the flag disappeared), my date format problems in FileMaker also disappeared!!

Oh happy day.......! ???

Edited February 15, 2006 by Guest

[comment]

IMHO, point 1) is the only relevant point here - partially. When you select a preset format and customize it, the format is automatically labeled 'Custom'.

[jonas234]

I have quite a similar problem which I wasn't able to solve yet.

I have the following data-format set in my system-preferences (Please note that my system-language is German).

http://www.merlin.ch/einstellungen.gif

I want to have my dates in the following format:

dd.mm.yyyy.

But now FileMaker doesn't want it to do it this way and ignores my system-settings.

I've exported some data from an old FM 5.5-DB and converted it to FM 8.

Now when I tell FM to use the file-settings for the date it's correct.

http://www.merlin.ch/format_1.gif

When I tell FM (or the file) to use the System-format then I have dates in a wrong format (dd/mm/yyyy). I want no slashes but points.

http://www.merlin.ch/format_2.gif

Does anybody have an idea what here could be wrong? I'm really desperate. :P

[comment]

Have you tried cloning the file under your current system?

[jonas234]

Yep, doesn't help.

[comment]

What about new files - do they behave correctly under "Use system settings"?

If yes, can you post your file?

[jonas234]

Ok, thanks to your help I've come a little bit further.

A new file works how I want it. When I save a clone and add there new data it works also. But when I import data from a file (the one I posted the screenshot of) i get the slashes. So it seems like an import problem.

[comment]

The problem is that the imported data has slashes - or at least FM thinks it has slashes. It is the same as if you have entered dates with slashes (FM will always accept slashes, no matter the settings).

Try defining a calculation field (result is Date) with one of the following formulas:

= YourDatefield

= GetAsDate ( YourDatefield )

= Date ( Month ( YourDatefield ) ; Day ( YourDatefield ) ; Year (YourDatefield ) )

If one of the calculations converts the dates to the correct format (check by clicking into the calc field), change it into a date field and delete the imported date field.

[jonas234]

None of the formulas converts it to the correct format, but when I change the date in the first field manually to the point-format it stays there.

But it cannot be the solution to change several 10'000's of dates to the point-format.

[comment]

If the last formula doesn't do it, then something is wrong. Check that the file is set to 'Always use system formats' under File Options.. > Text. Also make sure you ARE working with a CLONE (not a copy) created under the current system. Now try the last resort:

Define a calculation field Calc1 (result is Number) =

YourDatefield

Define a calculation field Calc2 (result is Date) =

Calc1

If that doesn't work, you may need to export your data to a text file and re-import it. When you export, do it from a layout where the date field has a custom date format, so that it displays as dd.mm.yyyy (when you don't click into it) and check 'Apply current layout formatting...'. If the exported file still has slashes, replace them in TextWrangler, then reimport.

[comment]

Another option: click into the field, select Edit > Find/Replace and replace "/" with "."

[jonas234]

Thanks for you help.

It still doesn't work and I also think that there is something wrong.

These database is converted and was running under fm 5.5 before. Could this be a problem?

See the following screenshot from a new file created on my machine. The first field is just a date-field in which I typed a date.

The second field is a formula-field with

= Date ( Month ( YourDatefield ) ; Day ( YourDatefield ) ; Year (YourDatefield ) )

and then I have again the slashes.

http://www.merlin.ch/date.gif

[comment]

We have had several reports here of similar problems with converted files - but IIRC they were all solved by cloning.

I would like to eliminate a possibility:

Please create a NEW file, with one date field, and one calculation field (result is Date) =

732363

Do NOT format the fields in ANY way. Click into the date field, and select Insert > Current Date. What do you see in each field?

[jonas234]

The following:

http://www.merlin.ch/date2.gif

I just heard, that some other users with a "Swiss" MacOSX have similar problems. It could also be a bug of FMP 8, which would surely be a shame.

[comment]

OK, so instead of eliminating a possibility, we have confirmed it: the problem is with the system format. Looks like Filemaker is ignoring it.

Try changing one of the parameters in the International System Preferences, and putting it back - just to get the Region to say Custom instead of Swiss. Then repeat the last test.

[jonas234]

Custom doesn't work either.

When I take German it works, but this cannot be the solution because then the number-formating is completely wrong.

So it looks like a grave FMP8 bug. ;)

[comment]

Yes, it looks like a misunderstanding between the OS and FMP - you should report it as a bug. One last thing worth trying is to start with German and customize it to match the Swiss settings.

On the bright side, I believe you can format your date fields to DISPLAY the way you want, by applying a date format in Layout mode. So the only time you will be annoyed is when you click into the field.

[jonas234]

Problem is (at least this says my boss who wrote the tool many years ago) that all the calculations, etc. are based on the format with points. I'll have a look at it. And I've already reported the bug.

Anyway, thanks for your patience and your great help. ;)

[comment]

Tell your boss that a calculation should never depend on the format of the data.