tom24569 Posted October 19, 2007 Share Posted October 19, 2007 I've imported a bunch of records that all have a text field to display a date, in the format "April 3, 2005". Is there a way to change these text fields to working date fields? Link to comment Share on other sites More sharing options...
LaRetta Posted October 19, 2007 Share Posted October 19, 2007 To change them to working date fields, they must BE date fields. If you simply change them to date field as they stand, they will be invalid dates and if left as text (even if they look like true dates ##/##/####) then FileMaker can't evaluate them as dates. So you must make them dates. The safest way is to: 1) Back up first 2) Create a new Date field called newDateField 3) Run Replace Field Contents through newDateField with calculated result of: [color:blue]Let ( [ m = 1 + Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( textdate ; 3 ) ; 1 ; 1 ) / 3 ; d = MiddleWords ( textdate ; 2 ; 1 ) ; y = RightWords ( textdate ; 1 ) ] ; Date ( m ; d ; y ) ) 4) After verifying it worked and if you truly want to preserve the old textdate field (ie, if you have it used throughout your solution and don't want to find it and respecify it as the new field), then now change it to a date. Then run Replace Field Contents through it, specifying by calculation and only put: [color:blue]newDateField 5) Then you can delete the newDateField. ... but be sure it is correct at each step; it's easy to make a mistake. NOTE: This following sentence in green is not quite what I meant. See below for clarification. [color:green]You could instead just convert your textDate to proper date and THEN run Replace Field Contents[] on it. But if anything goes wrong ... better to handle it in two steps so you can check yourself. Also note that, if any of the text dates are improper, ie, you have February 29, 2003 (which is NOT truly a leap year), then FileMaker will create the date as 3/1/2003. Link to comment Share on other sites More sharing options...
tom24569 Posted October 19, 2007 Author Share Posted October 19, 2007 Laretta - thanks, that worked beautifully! Link to comment Share on other sites More sharing options...
LaRetta Posted October 19, 2007 Share Posted October 19, 2007 You could instead just convert your textDate to proper date and THEN run Replace Field Contents[] on it. Error ... I meant the reverse of course. You can't make it a date and then run the calc through it because FM won't recognize it as a date and it'll break on the conversion. I meant run the calc thru it and then change it to a date. I hate it when I mis-talk. But you got it using my first suggestion anyway. :smirk: Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6168 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now