Programatically change datatype?

[CliffHanger9]

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!

 

[FoFa]

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

 

[CliffHanger9]

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

on the fly? elaborate please

thanks!

 

[FoFa]

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.

 

[mresann]

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.

 

[Pat Hartman]

By the way, many programmers disdain the Date/Time mode and store dates and times as Text data.

- I think the case is more likely that many programmers define dates as date/time but think of them as text since that is what we see as humans and they have no clue that dates are actually stored as double precision data types.

Here's one example:

Use ALTER COLUMN to change the data type of an existing field. You specify the field name, the new data type, and an optional size for Text and Binary fields. For example, the following statement changes the data type of a field in the Employees table called ZipCode (originally defined as Integer) to a 10-character Text field: 

ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)

You can use DAO or ADO to run this query.

You can also search help (VBA help) for createfield method to see a technique that uses DAO coding to modify the fields collection of the tabledefs collection.

 

[CliffHanger9]

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.

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


Thanks guys!