my old records were entered in the format of:

dd-mm-yyyy for the Date, if i would like to change those records to the format of MM-dd-yyyy. what is the best way without messing up my records.

If the entry was into a date field, it doesn't matter how they were entered. You can display them any way you like (go to Layout mode, select the field, and choose Format > Date... from the menu).

See also this thread.

i get ? in the date field even if i format date to mm/dd/yyyy.

Copy__2__of_Ghcases_Clone.zip

Edited April 20, 200619 yr by Guest

A question mark in a date field usually means the entry is not a valid date. Better start your story from the beginning, so we can understand what happened and how it can be fixed.

---

I cannot get your atttachment to open.

Edited April 20, 200619 yr by Guest

i edited my post by adding the zip file.

ok the date field is entered in the past in the format dd-mm-yyyy. now i am working on updating the file with different layouts and new relations and tables so i am experimenting with a clone that was made in fmp 7 to see how it works with fmp8.

the date field can display correctly all the old entries if i choose the option: leave date formatted as entered.

but i cannot search dates more than 12 for the day

its somhow reads it as invalid month.

if i choose (date format mm/dd/yyyy), it displays the question marks but when you click in that field it displays the entered date in the old format . so click in a records'? and you get 29/5/1999 for that particular record.

i was wondering if i can replace with calc the date fields so it will format it mm/dd/yyyy.

Ghcases.zip

Edited April 20, 200619 yr by Guest

i added a new zip file.

thx for your patience.

I am afraid I still cannot open your attachment - are you sure this is a valid zip file?

Anyway, I am fairly certain Filemaker does not recognize your data as dates. When you click into a field, you always see the original entry, so that doesn't mean anything. Was this data imported?

yes it was imported. i tried to download my zip file and i opened it.

i can e mail it as an attachment to you

Please describe the entire process - where did the data originate, in what format, do you still have the original data, etc.

i have 2 fields in my old file:

Date, Case #, and the rest of the table fields..like patients name, referring physician...etc.

i used to enter the case # manually in the format of:

06-05000. 06 referrs to the year,03 to month,000 the case# and at the end of the month i change the number ### back to 000.so the first day of April the case # field will be: 06-04000.

the date field is create on entry. in the format of dd/mm/yyyy.that was in fmp 7.

i tried to auto enter rather than manually doing it so for date: Get(CurrentDate), Case #:Month_Year & Right("000" & (Count(Self_On_Month_Year::Date));3)....and added Month_Year field as calculation: Right(Year(Date) & "-0"&Month(Date);5).and this is in fmp8

in the new file the Date field format is set to mm/dd/yyyy.

when i create a new record it works and no problem.

when i import the old records i get the ? in date field and in Month _Year field.

What happens in the old file, if you go into Layout mode, select the Date field, and change the format to say "Dec 25, 2003"? Do you still see valid dates?

any change in the date format of the old field other than: leave date formatted as entered , gives question marks.

i think in 1997-2003 i used the option custom in the date entry and got it dd/mm/yyyy.

you still can find ranges so it recognizes the field as date and not text or other.

i will try to zip the file and upload it , am not sure what is the problem with my winzip.

Ghcases_1.zip

Edited April 21, 200619 yr by Guest

i think it is getting confused between the two formats...or me gettin confused...lol

it some how reads 12/4/1998 ok no question marks.

but it reads 29/3/1998 as a question mark.

because 29 is not a valid month and it suppose to be <12.

to have more fun it means with the read 12/4/1998:dec.3d.1998. and the original file refers to april/12/1998.

as i mentioned with new files no problem. old ones .....confusion.

If this is your original file, then the problem is much older than you thought. I don't know what you did, but you have a lot of invalid date entries there. It looks like this data was originally entered as TEXT, then changed (or imported) to DATE.

I tried to fix them as best as I could, but only you can tell if they are correct. As you have noted yourself, some entries can be read in two ways. If the data entry is not consistently following the same rule, then it cannot be reconstructed reliably.

FYI, the date format you set in Layout mode has nothing to do with this. This only changes how a date is displayed. What matters is your Windows/file date settings (see the thread I linked to earlier).

Ghcases_1.fp7.zip