Need Help with a Find please

[mrswombat]

Hello,

I'm working in a database that, for whatever inane reason, has a date field that was created as a text field. Now, years later, I have to do a find for a range of dates (an entire fiscal year, over 1,000 records) using this text field and of course, it's not working.

I can't change the field to a date field without messing up the data from thousands of previous records (or can I?), and I can't figure out if there is a way to create a date field to replace this text field, while pulling in the values from this text field.

Help please and thank you for any ideas or suggestions.

[doughemi]

Create a new calculation field DateAsDate = GetAsDate(YourTextDateField)

[mrswombat]

Is this calculation applicable to FM Pro 9, because I don't see the "DateAsDate" function listed...

[Vaughan]

No, don't do that: the original data could be lost.

I'd create a new calculation (date) field in the database and initially set it to GetAsDate(YourTextDateField). This will possibly clean up 80%.

Then go through the records and identify those that are not correct because of data entry issues. Craft the calculation to deal with these.

For instance one record might have 1 Jan 2010 entered into it. The calculation would be changed to

Case(

field = "1 Jan 2010" ; Date( 1 ; 1 ; 2010 ) ;

GetAsDate(YourTextDateField)

)

Alternatively if there is just one record with the bad date then correct the date field itself.

Rinse and repeat until all records are done. Then copy the calculation and paste it into the Replace command and use it to replace the original date field. Delete the new calc field.

Backup before, during and after doing this cleanup.

[comment]

a database that, for whatever inane reason, has a date field that was created as a text field.

You left out the most important detail: in what format are the "dates" entered in this field?

[doughemi]

Is this calculation applicable to FM Pro 9, because I don't see the "DateAsDate" function listed...

DateAsDate is the name of a calculation field, not a function.

Vaughan, how would this lose data?

You and comment are right: I assumed that the date is in the format mm/dd/yyyy or mm/dd/yy; bad move on my part.

[mrswombat]

Unfortunately, the date in the text field is in a "June 30, 2011" type of format..perhaps this is why my calculation field is not working? I tried what Vaughn said to do and I keep getting either a "?" or a strange "Sat0, 0000"

[mrswombat]

Ok, I tested again and the fact that the text field date is set in the "June 30, 2011" format is definitely the culprit.

Any other ideas? Thank you again...

[comment]

Make the calculation field =

Date (

Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; Left ( Textfield ; 3 ) ; 1 ; 1 ) / 3 ) ;

MiddleWords ( Textfield ; 2 ; 1 ) ;

RightWords ( Textfield ; 1 )

)

Set the result type to Date. Next, perform a find for ? in the calculation field - this will show records that do not translate to a valid date. If everything looks fine, change the calculation field's type to Date. I would also keep the old data as is, since with thousands of records you never know...

[Vaughan]

Vaughan, how would this lose data?

You're overwriting the source data with the result of the calculation and the source data is gone forever.

If the date is invalid it may end up as "?" but at least you know it's wrong. If the calculation ends up changing 1/1/49 to 1/1/2049 instead of 1/1/1949 how will you tell? It's a silent failure.

The other thing that Comment alluded to is the possibility of variations of date entry due to regional locations. The solution you create for a client in US probably won't work for a client in Japan or Australia because the date format is different.

[LaRetta]

But Vaughan ... doughemi said, "Create a new calculation field DateAsDate = GetAsDate(YourTextDateField)"

Touching the original text-date field was not mentioned. :wink3:

[Vaughan]

Yep OK I mis-read that. Sorry Doug. :(

[mrswombat]

Wow Comment that calculation worked like a charm. Thank you all for the help again!