Inserting and showing date like dd-mm-yyyy

[Auditiaan]

Hello everybody,

I'd like to know if it is possible to insert dates in dd-mm-yyyy format.

I already know how to show it like that. That ain't hard, but inserting is a lot harder, while keeping the field definition as date.

Any help is appreciated.

Greetings,

Auditiaan

[QuinTech]

Hi, Auditiann. Try setting the field up as a text field and creating your own validation-- something like:

Case(

...Middle(theField;Position(theField;"-";1;1)+1;Position(theField;"-";1;2)-Position(theField;"-";1;1)-1)="1";

.....TextToNum(Left(theField;Position(theField;"-";1;1)-1))<=31 and TextToNum(Left(theField;Position(theField;"-";1;1)-1))>0;

...Middle(theField;Position(theField;"-";1;1)+1;Position(theField;"-";1;2)-Position(theField;"-";1;1)-1)="4";

.....TextToNum(Left(theField;Position(theField;"-";1;1)-1))<=30 and TextToNum(Left(theField;Position(theField;"-";1;1)-1))>0;

et cetera

Thus, if the month portion is 1, the validation makes sure the day is between 1 and 31 inclusive; if it is 4, then the day must be between 1 and 30. I skipped the February entry because you'll have to check for leap year (divisible by 4 but NOT by 200) and i'm a bit too lazy to write that calculation right now.

This could get pretty detailed; off the top of my head, you'll need to check for (1) all the values being positive integers (2) the month being between 1 and 12 (3) the day being in an appropriate range. Maybe a custom function would be your best bet...

HTH,

Jerry

[TerenceM]

I suspect Filemaker uses the default date format specified by the OS. On a Mac, you would change your date format settings in International Preferences, and FP7 should start using dd-mm-yyyy for data entry. I imagine there's a similar setting on Windows XP. Of course, that would change your date format in all other applications, so it depends on what you want.

You can also use the old "stacked fields" trick (see attachment), with a text field that you enter date into and a date field superimposed on top of it for viewing. But you'll still have to include data validation to make sure the date is realistic (without the validation, incorrect dates get scaled in interesting ways. e.g., Jan 32nd gets changed to Feb 1, and the 1-15-2004 gets changed to March 1, 2005. Makes sense, but probably not what you want).

I thought you might be able to use a Self-Join lookup to enter the formatted date back into the text field, but it didn't work (the lookup pulls the date out in the format specified by the OS, not by the layout). Without this, it becomes tricky to search the date field. So in the end, you're better off sticking with just a text field and doing everything (formatting/validation) yourself, or changing the OS preferences.

-Terence

dateformat.fp7.zip

[Auditiaan]

Thanks for replying, Quintech and Murphy. I didn't want to use the first solution, since you have to deal with a lot of things, like a leap year.

I viewed the attachment, thanks for that, it became very clear to me. I still think that changing the system settings is the best solution for me. Tried it once, but didn't notice that my system settings were still US settings

Thanks again for the help, greetings Auditiaan