Changing Data/type in table with Module

[cmartin1986]

I have 14 fields first one is part number and the next 13 are different Product Lines, I need to change the last 13 fields to date/time and leave the first one as a text field. I have to run a make-table query everyday which will switch the fields back to text. I've been switching it to date/time manually but im building a chart with this info and need it to update automatically. Let me know if you can help.

Thanks!

[cmartin1986]

bump...........................

[MMcCarthy]

Without a lot more information its difficult to know how to help

However, have a look at the CDate() function.

[NeoPa]

What's the SQL of your make-table query?

[cmartin1986]

I was able to obtain a soloution to this problem and just thought I would share it with the forum.

Sub AlterFieldType(TblName As String, FieldName As String, DataType As _
   String, Optional Size As Variant)
Dim cd
If IsMissing(Size) Then
DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" & FieldName & _
   "] " & DataType
Else
DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" & FieldName & _
   "] " & DataType & "(" & Size & ")"
End If
End Sub
 
 
Public Function Update_GKNStatus()
DoCmd.SetWarnings False
Screen.MousePointer = 11
 
DoCmd.OpenQuery "qry-getdates"
AlterFieldType "getdates", "status", "date"
AlterFieldType "getdates", "line unit 9997", "date"
AlterFieldType "getdates", "Line Unit 0002", "date"
AlterFieldType "getdates", "line unit 9998", "date"
AlterFieldType "getdates", "Line Unit 0003", "date"
AlterFieldType "getdates", "Line Unit 0004", "date"
AlterFieldType "getdates", "Line Unit 9901", "date"
AlterFieldType "getdates", "Line Unit 0005", "date"
AlterFieldType "getdates", "Line Unit 0006", "date"
AlterFieldType "getdates", "Line Unit 0007", "date"
AlterFieldType "getdates", "Line Unit 0008", "date"
AlterFieldType "getdates", "Line Unit 0009", "date"
AlterFieldType "getdates", "Line Unit 0010", "date"
 

[NeoPa]

That's a neat solution and thanks for posting it.

Having reread the question, it occurs to me to suggest a method I usually use to avoid the necessity for this.
I typically import the data into an existing table (where every last detail of the design is set up and saved) after first clearing the table using :

DELETE FROM [Table]

I don't mean to minimise your solution though, it's good and I may well remember it for my own use.