Date format problem

[Bapak]

Using FM7. My dbase has about 15000 records, all of which have been imported from various sources, some Excel but mostly earlier versions of FM. There is a date field where the date displays correctly for all except for about 700 records where it shows up as ? ... Have tried all different date formats but still ?... back at the source, the date format is correct. Any suggestions?

Cheers, Bapak

[Lee Smith]

Is this a Date field or Text in the old file?

What does the actual year part of your date look like when you click in the field. i.e. 02 or 2002?

Lee

[Bapak]

It is a date field and year has 4 digits, i.e. 2003

Bapak

[LaRetta]

With imports, many things are possible. We would need to know how the date on those 700 is broken to know how to script fixing them; because they must be fixed at the data level. And just to verify, it is a DATE field in your new file as well?

Just to see, try this:

Create a calculation (number) with: IsValid(yourDateField)

Search for 0 and see if the same 700 come up. This will verify that they contain text or wonky characters which need to be stripped. Never mind ... of course they are invalid or you wouldn't get the ? in a date field.

[Raybaudi]

I'm sure that you have enlarged enough the field !

[LaRetta]

That won't matter with dates, Daniele. You can scrunch them good and they'll still display part of the date. And if the other dates display fine, it's not Field Format either.

Are the month/days reversed, as in an International setting? Has this import or file been exposed to different computer date preferences? Because it sounds like either text or characters are mixed in with part of it ... or the dates are invalid for your computer preferences. In which case, SOME of the date fields would be fine because they are 'legal' even if reversed, ie, 1/11/2005 and 11/1/2005 are both valid. But 15/3/2005 is not valid. Can you verify that the months listed on those ? records are valid months?

Edited September 4, 2005 by Guest

[Raybaudi]

You can scrunch them good and they'll still display part of the date.

correct, LaRetta !

[comment]

You can search for invalid dates directly in the date field, by entering a question mark.

[LaRetta]

Of course. I knew that too. Momentary logic brownout ...

[Bapak]

Thanks folks for your assistance. Problem resolved.. It appears that the 700 odd files imported from Excel was in text format, despite earlier indications that it was in date format.

Cheers, Bapak