I would first capture the entire date as a string, called OriginalDate.
Now create a calculated date field called FinalDate (or whatever the date really represents) whose formula is ctod(strip(OriginalDate," "),"m/d/y").
This first removes all the spaces from the text date, then converts the text to a true date format.
That ought to do it for you. Please do let us know how you make out.
Todd, you got in there while I was typing.
I seem to recall Mike U referring to 'elegant solutions' on occasion so I claim no rights to the concept!
My first inclination is to go with Nick O-P's suggestion and use the Strip function.
If the entire file was like that and all spaces were to be removed it might be worth considering using MSRP on the file prior to input.
Otherwise an alternative to SUBSTR in this example might be to use one (or both, though I can't think of a good reason to do so) of LSPLIT or RSPLIT. But in effect the formula would be pretty much the same as for SUBSTR so it would seem to make little difference which way one goes.
The fields look like date fields. Are they? And does the problem only arise with date fields or are other fields involved? (I seem to recall we had an example of a similar problem relating to a report's header section a while ago ...)
Edit to add that Data Kruncher's post has the full formula I would use for the calculated field. The only potential issue I can see from specifying the field as a date fields is that the "05" would be presented as "2005" ( I assume your settings would not imply an "05" in any other century!) Would that be a problem?
[size="1"][ March 07, 2006, 06:46 PM: Message edited by: Grant Perkins ][/size]
Grant, my apologies for incorrectly attributing the phrase "elegant solution" to you. In light of my error, I've edited the related posts. /b[/quote]Todd, there was really no need to go that far ...
In my defense, though, it seems appropriate to associate you with "elegant solutions" since, of all the people who have posted on this forum, you have provided far more of them than anyone else. /b[/quote]Well, thank you kindly sir. It's very nice of you to say so. Perhaps I am lucky in that the time difference for the UK has often allowed me to research and post a suggestion before the sun rises over North America from where many excellent suggestions emanate (when they get the chance!!!)
Back to the subject of the post though - does Ontiveros have a solution I wonder?
I have found a solution in having a program called Miraplacid 4.0 take all reports used, combine them into one single report and output them in a different format which removes spaces between characters. Thank you both for your advice, though. If it were only a date field, the STRIP function would have worked perfectly. Have a nice day.
I have found a solution in having a program called Miraplacid 4.0 take all reports used, combine them into one single report and output them in a different format which removes spaces between characters. /b[/quote]That sounds like an interesting program to be aware of, though I guess some judicioous editing using something like vi could give a similar result.
Does Miraplacid have some clever setting to allow valid spaces to be retained - for example leaving a single space if the is a space pair? (The alternative is a 3 or more pass routine to change space pairs into something else in pass one, then remove all the remaining spaces, then convert the space pair identifier characters back to a single space. That would be a simple situation - it could get more complex of course.)