change dates to dd/mm/yyyy NOT using date format display

[kiwiora]

Hi,

I have users typing in d/m/yyyy when all dates should be entered as dd/mm/yyyy.

I understand I can alter the formatting to display as dd/mm/yyyy but when you enter the field, it reverts to displaying d/m/yyyy. The same with exporting. I know you can export as formatted, but for various reasons which I won't get into, I am unable to use this option.

So, I need to be able to somehow insert a calculated result to alter the d/m/yyy to be dd/mm/yyyy.

I'm trying to figure out using something like counting the characters left of the / and if only 1 character adding the zero but i'm not so offay with the functions so any help would be appreciated.

thanks in advance.

[comment]

First, set your system (Windows) date format to dd/mm/yyyy (with leading zeros for day amd month).

Next, set your file to 'Always use system formats' in File Options.

This should take care of data entry from now on. For existing records, replace the datefield's contents with GetAsDate ( datefield).

[kiwiora]

Thanks comment, I have those settings set.. but if I type in 3/3/2006

it always inserts it as

3/03/2006

So that's why I thought that perhaps I should do a calc???

[comment]

I don't have Windows. On Mac OS X, if I set the system's short date format to use leading zero for the day, then type "1/1" into the field, I get 01/01/2006.

What do you get when you set a calc field (result is date) =

GetAsDate ( datefield)

or just = datefield?

[kiwiora]

same thing

3/03/2006

short date is set as dd/mm/yyyy

hmmmm

Edited January 13, 2006 by Guest

[sbg2]

Some testing I did on my Windows based PC *note my setting are mm/dd/yyyy but I get similar results.

1/1 results in 1/1/2006

1/01 results in 1/1/2006

1/01/2006 results in 1/01/2006

1/01/6 results in 1/1/2006

01/01 results in 1/1/2006

01/01/2006 results in 01/01/2006

I tried to us the following for an Auto-Enter Calc

Let ( [

Day = Day(date);

Month = Month(date);

DM = Right("0" & Month;2) & "/" & Right("0" & Day;2) & "/" & Year(date)

]

; DM)

Typing 1/1 still resulted in 1/1/2006. I also tried using a Set Field script step with above calculation and had the same result, it wouldnt change the date to 01/01/2006.

The only thing I could come up with was to force the user to enter all 10 digits of the date. Validate by calc = If(Length(date) >0 and Length(date) < 10 ;0;1). This works but is very annoying and will not fix the problem with dates that were previously entered.

Lastly I tried a simple import

01/01/2006 resulted 01/01/2006

1/1/2006 resulted 1/1/2006

01/1/2 resulted 01/01/2002

1/01/2006 resulted 1/01/2006

1/1 resulted 01/01/2006

1/1/06 resulted 01/01/2006

[comment]

I am curious what do you get with the attached file (without changing anything in the file's settings).

Untitled.fp7.zip

[sbg2]

I hate to keep butting my nose in here but this had me curios again. I opened comments file and lo and behold the date showed as 01/01/2006. I erased the 01/01/2006 date and typed 1/1 and hit enter.... low and behold date changed to 01/01/2006.

Then I went back to my test file from my post above. I figured maybe since I created the date field before I cahnged my systems settings from m/d/yyyy to mm/dd/yyyy that caused the problem. I added a new date field but no luck, still got 1/1/2006.

Lastly I created a new database with 1 date field, no formatting, no auto-enter calcs, just defined a date field and created the table.... In this file typing 1/1 results in 01/01/2006!

It appears that FM attaches the date definitions to a file when it is created?

[comment]

That is correct. See Help > FileMaker Pro basics > Managing files > Opening files with foreign system formats

[kiwiora]

Thanks guys, you've been busy whilst I've been away!!

It appears that FM attaches the date definitions to a file when it is created?

grrr well that's a fat lot of use to me : Especially when I created the database before I realised this issue by testing the data :

There's a few funky things that filemaker has as "expected results" .. but expected doesn't necessarily mean correct - case and point when you get(desktoppath) it does some weird a$$ result for Windows.

[comment]

You can change the date/time formats at any time, either temporarily (by setting the file to 'Use System Formats' under Format menu), semi-permanently (by setting the file to 'Always use sytem formats' under File Options), or permanently by cloning the file.