Dates

[Ravi]

How can I convert a Text field to a date field? I
attempted to change the field properties and it deleted
all the dates I had. Any Ideas???

Thanks in advance

Ravi

 

[Sam]

Access usually has a pretty good go at doing this on the fly, it seems your
dates must be entered in some odd way.

Can you describe the format of the dates, or at least give some examples...

Sam

 

[Sam]

You'll need to turn them around and add separators. You could do this using
an update query. First ensure the length of your text field is at least 8
characters. Then execute something like...

UPDATE tblYourTableName
SET tblYourTableName.strDate = Right([strDate],2) & "/" & Mid([strDate],3,2)
& "/" & Left([strDate],2)

This would put the text dates in the form dd/mm/yy, if your system is set to
US then I think you'll need to adjust this by swapping the Right and Mid
functions so you get mm/dd/yy.

Once this is done, I'm pretty sure you can just change the field type to
date/time and Access should do the conversion.

HTH
Sam

 

[Tim Ferguson]

This would put the text dates in the form dd/mm/yy, if your system is
set to US then I think you'll need to adjust this by swapping the
Right and Mid functions so you get mm/dd/yy.

I'd vote for adding a new column and updating it with a proper date value:

update mytable
set mynewdatefield = dateserial(
cint(mid(myolddatefield,1,2)),
cint(mid(myolddatefield,3,2)),
cint(mid(myolddatefield,5,2)))
where len(myolddatefield)=10


This sidesteps all the Regional Settings conflicts. The advantage of making
it a new column is that if there is an error then you still have the
original data to go back to. Once you are happy that the new values are
right, then you can drop the original column.


Hope that helps


Tim F