Is the field defined as type "Date" or is a text field?
you can try substitute by Char-# instead of " ' ".
' = char (39)
" = char (34)
substitute ( txt; char (39) ; "" )
It's char (39) according to the sign.
I'ld recommend to test char (34) as well in case there's a misinterpretation by Windows System Preferences, for example.
Another test would be to copy/paste this unwanted character into this site to proof the char-N°:
email 'stripping' my replies again...
here's what I said:
as quote marks may be curly (educated) in imports or pastes, there are actually several characters one may need to substitute.
char(39) = single quote, un-educated, uncurly
And your FONT may also come into play for some characters, so be fore-warned.
here's what I said (reply-by-email)...
GetAsDate ( Substitute ( myField ; "'" ; "" ) )
the YY instead of YYYY may or may not be a problem and additional parsing needed. But try the GetAsDate() to see if that helps.
You could also use the Filter command. The date field would only have numbers from 0 to 9 and the slash character.
I am overwhelmed by the quick responses, thankyou very much!!
I will let you know how I go
again thankyou all
was unable to get any of suggestions working,
I created a new field as a text field Inv Date1
, brought data '01/07/2014' as text then alter my original field Inv Date to be Substitute (Inv Date1;"'";"") as a date field
Ok, there were a great many suggestions here and any of them could have solved your issue. Could you respond with the output from each one of the separately so that we hone into what is going?
Although the text files you're importing data from sound simple enough, it might help if you include one (as redacted as you like, as long as it when you try to import it it has the same problems)(use advanced editor and attach text file). Also give a more exact description of how you do things - for instance, you presumably import the files straight into the fields; giving us the script step that you use to convert the text entry into the date (for each suggestion) would allow us to help you.