Data type Conversion

  • Thread starter Thread starter Arslan
  • Start date Start date
A

Arslan

I tried to find an answer for this but most of the ones I found were
not really like the problem I am facing.
I am downloading a report in text format and the date fields are
formatted as 'Monday, December 12, 2005' with the hyphens. I need to
convert this to date format so I can sort / compare data. Access
imports this field as text and trying to change the data type to date
causes the data to be deleted. How can I go about doing this.
Thanks
 
CDate(Mid$([TestText],InStr(1,[TestText]," ")+1))

Replace "[TestText]" with the name of your text field.
 
Thanks for the reply, however, imported field has ' both before and
after the date text. As I noted above the fields read exactly like
below.

'Sunday, December 11, 2005'

I am manually removing the sign using find / replace for the entire
column, not the most efficient way of handling this.

Any other suggestions?
 
Try the following:

Left(YourDate,Len(YourDate)-1) is the string less the last character
'Sunday, December 11, 2005
Now you can use Instr to find the first comma space and used mid to chop the
string after the comma space

Then you can use the DateValue function to convert the string to a valid date.


DateValue(Mid(Left(YourDate,Len(YourDate)-1),Instr(1,Left(YourDate,Len(YourDate)-1),", ")+1))
 
Back
Top