change field type from text to date

[jguscs]

I'm trying to overcome some problems I'm experiencing with my SQL statement that's supposed to update the field type of every cell of a particular column from a text value to a date value.

The contents of each cell are in the format x/xx/20xx, so there shouldn't be a problem converting, should there?

DoCmd.RunSQL "UPDATE [" & File & "] SET [" & File & "].[Date Add] = CDate();"

I'm sure there's something wrong with my SQL syntax.
Please help.

 

[GumbyD]

Just a clairification: Is the datatype at the table level a Date/time? If it is not then I don't think access is going to let you update this data. If you want to update a datatype you should be able to go into the table design and just change the datatype (as long as the data does not break any of the datatype rules that should work). If you have some more information about what you are attempting to do it may help.

Thanks,

GumbyD

 

[jguscs]

Right, the datatype at the table level is TEXT.
That's precisely what I want to change to Date.
The reason why it's text is because I began trying to solve the problem of importing an XLS file to Access and Access mis-designating the type of each field. (Example: Access would designate a type as double or integer instead of text). So, I used the following method to get all of the fields to a text format.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, File, FilePath, False
DoCmd.TransferText acExportDelim, , File, "C:\TempFile.txt", False
DoCmd.DeleteObject acTable, File
DoCmd.TransferText acImportDelim, , File, "C:\TempFile.txt", True
Kill ("C:\TempFile.txt")

Now, there are fields that should be designated as type DATE.
So, I need a method that will change the type of each specified field from Text to Date. And I heard CDate() would do the trick, so I incorporated it into an UPDATE query.
(I need the field type change to be done automatically; behind the scenes.)
That's my story.

 

[GumbyD]

Now I got you! When you do the import move the data into a temporary table with all the fields as text. After you have the data in access then move it into the destination table where the datatype is Date/time. In the append query setup the field as DateFieldName:Cdate([TextDateFieldName]) and then append it into the destination table. That will get it into the final table with the correct datatype.

GumbyD

 

[Pat Hartman]

You need to use either DAO or ADO or DDL to modify table structure. An update query won't do it.

To find help for these things, open help in the database window (NOT a VBA window). Select the Table of Contents tab. Toward the bottom, you'll see the entries for ADO, DAO, and SQL. Drill down to what you need. There are code samples. Or you can search the archives here for code samples.

 

[jguscs]

GumbyD:
I'm sure you're right (I'm sure Pat's right, too, but I'm much closer to understanding transfer queries than ADO/DAO/DLL).

I'm going to have to ask you for some assistance, however.

I intend to set up a "template" table with certain fields designated as type Date.
The template table will be copied each time this algorithm is performed so the original template only has to be created once.

How do I set up an append query to move data from the temporary (import) table to the (copy of the) template table?
I'm not very good with queries.

Also, the following situation is possible:
the temporary (import) table may have less columns than the template table.

 

[GumbyD]

Once you have the data in the temp table with all of the datatypes set to text, you will need to run a query the appends the record to the correct table.

In the query window bring in the temp table (with the text fields) and double click or drag the fields you want into the grid. On the menu bar go to Query and select "Append Query." It will ask you which table you want to append to, select the database table with the data types that have date/time. After this a new row will appear in your query grid, Append To, this is where you select the field you want the data to go into. Here is where you use the expression I wrote earlier expr1:cdate([Your text field name]) this will convert the text data to a date/time format and will allow us not to get the data type mismatch error. After you are all set you can run this by pressing the red Explainaiton point on the tool bar or you can run it in code with docmd.openquery "your query name" or currentdb.execute "your query name" (this one does not show the prompt boxes). I hope that helps you, you may want to play around in the query or look at the help to get more familiar with action queries.

GumbyD