Strange problem involving date format/representation

[jetwolf]

Hi,

I am experiencing a strange problem related to the format/representation of dates, where on some computers the day of month is being interpreted as the month.

Here's what I have:

- A field named "ReportDate", of type "Text", with the properties "Auto-enter" and "Calculation replaces existing value". The specified calculation is: MonthName ( Self ) & " " & Year ( Self )

- A "Drop-down calendar" control, backed by the "ReportDate" field

Basically, this implements a calendar control where only the month and year are relevant, and the day of month is disregarded. For example, suppose someone chooses the date "April 24, 2012" using the drop-down calendar, the string "April 2012" is supposed to appear in the field.

On my computer, this works just fine. However, one of my clients is reporting that whenever they use the calendar to select a date, a different month appears in the field than the one they've selected, or sometimes a "?" appears. After going through a few examples, I discovered that the chosen month is being disregarded, and instead the chosen day of month is interpreted as the month; so, for example if they choose the 1st of any month, "January" appears; if they choose the 10th of any month, "October" appears; and if they choose a day of month that is higher than 12, a "?" appears.

Any ideas as to what might be causing this? I am using Filemaker 11 Pro Advanced.

TIA!

[Lee Smith]

Check the system setting for date setting.

[jetwolf]

Check the system setting for date setting.

I'm not sure what exactly you mean by that. If you mean the system date set in "Date and Time Properties", it is correct.

[Lee Smith]

The computer settings dictate what FileMaker will use as its date setting.

If that is not the problem, the file can still be using those setting that were used by the computer that created it.

Lee

[jetwolf]

The computer settings dictate what FileMaker will use as its date setting.

If that is not the problem, the file can still be using those setting that were used by the computer that created it.

Lee

I still don't know what exactly you mean by "date setting". Could you tell me which OS dialog I should be looking at? The only OS dialog related to date that I know of is "Date and Time Properties", where you can set what the current date is. However, my program does not at any point query the current date, so I don't see how that would be relevant.

[Lee Smith]

I don't know the exact Name for it in Windows, but it is a control panel settings and it's how you set the time and date for your computer.

[comment]

If you want the field to show as "April 2012", then it would be best to make it a Date field and custom-format the way it displays.

[brian rich]

The problem is probably caused by whether the computer is set up to display dates in the form mm/dd/yyyy (US form) or dd/mm/yyyy (UK form)

The Control Panel that dictates this in Windows is called Region and Language

See Filemaker help for more information: http://www.filemaker...27.html#1028551

HTH

Brian

[Vaughan]

The problem is that you are applying date functions to text strings.

Either do as Comment suggests (date field with custom formatting) or make use a text field and enter the values. Or use 2 field, one to select the month name and one to select the year, and use a third calc field to concatenate them.

Regarding the date settings, you're assuming that everybody in the universe enters dates as mm/dd/yyyy. Not everybody does: in fact, MOST people on the planet do not. :D

[comment]

The problem is that you are applying date functions to text strings.

Actually, the problem is caused by a bug.

[jetwolf]

Regarding the date settings, you're assuming that everybody in the universe enters dates as mm/dd/yyyy.

Could you point out the part of my calculation that makes that assumption?

If you want the field to show as "April 2012", then it would be best to make it a Date field and custom-format the way it displays.

Thanks, I didn't know about custom formatting of dates! I will try that out and post back about whether it fixed the problem.

[jetwolf]

If you want the field to show as "April 2012", then it would be best to make it a Date field and custom-format the way it displays.

That fixed the problem! Thanks!

[Vaughan]

Could you point out the part of my calculation that makes that assumption?

It's a text field. The date picker is inserting the text "24/4/2012" or "4/24/2012" depending on your OS regional settings. You then use a date function MonthName ( self ) on the text, and FMP is forgiving enough to convert the text into a date and get the month name.

If another computer's OS is set up to enter "24 April 2012" as the date string, then the auto-enter will fail because it's not in the format mm/dd/yyyy or dd/mm/yyyy and the result will be "?".

Please explain WHY you need such a field.

Comment: what is the bug?

[comment]

Vaughan,

If a file is set to use current system settings and the current system setting is d/m/y, then two things should happen:

1. Using a calendar on a text field should insert a "d/m/y" string into the text field;

2. The expression GetAsDate ( text ) should be evaluated using current system setting (d/m/y).

The former is true; alas the latter is not: Filemaker insists on evaluating GetAsDate( text ) using the file's saved settings, regardless of the setting in File's Options.

[Vaughan]

Comment: thanks.