I have a text field showing dates as 12/15/10 or 7/5/10 (not 07/05/10).
Need to extract the month part and the day part. cannot convert field to DATE data type - nor will a date part function work due to single digits entered for some.
I have a text field showing dates as 12/15/10 or 7/5/10 (not 07/05/10).
Need to extract the month part and the day part. cannot convert field to DATE data type - nor will a date part function work due to single digits entered for some.
Will the CDate() function work? If not, you're probably looking at a custom function using the Split() function. You might be able to use Left() and Mid() along with the InStr() function to find the position of the first "/" and the InStrRev() function to find the last.
Problem is they set field as TEXT, then included other stuff in it -
for example:
3/15/10 1:15 pm
3/21/10 north of town
5/12/10 (or 5/13/10 after midnight?)
Ah, a moving target. If the space is reliably there, you can still try CDate() after using Left() with the InStr() function to find the space. Otherwise you're probably looking at a custom function to handle the various possibilities the field could contain.