Programatically change datatype?

Anyone have any ideas how to create a function that would change the datatype of a table field??

I have data that is imported to access so it is put in as text and I am hoping to run a macro to convert the text field to a DATE field so that the report can properly calculate things.

Thanks in advance for any insight!!!!!!!!!

rock on!

Why not base your report on a query and convert the text field to a date field on the fly?

FoFa said:

Why not base your report on a query and convert the text field to a date field on the fly?

Click to expand...

on the fly? elaborate please

thanks!

I would try cdate([MyTextDate]) and see if that works (simplist), if that does not work you can use a combination of DateSerial with LEFT/RIGHT and MID to create a datetime value.

If all the dates are stored in a consistant matter, try this. First, make a backup of a table. Next, open the table in design mode. Third, change the datatype to "Date/Time".

If you get an error, then the date formats were not recognized by Access as dates, and you will have to go through one by one to ensure every date is consistant.

NOTE: the new format will probably include "12:00 am" after every date, that is ok.

By the way, many programmers disdain the Date/Time mode and store dates and times as Text data. I disagree with that protocol, as Date/Time is actually a number, not a text, and can more easily be calculated programmatically. But to each his own.

mresann said:

By the way, many programmers disdain the Date/Time mode and store dates and times as Text data. I disagree with that protocol, as Date/Time is actually a number, not a text, and can more easily be calculated programmatically. But to each his own.

Click to expand...

THe problem with this is that i cannot run calculations for my reports on text values


Thanks guys!