By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Changing Data/type in table with Module

P: 37
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!
Sep 20 '07 #1
Share this Question
Share on Google+
5 Replies


P: 37
bump...........................
Sep 21 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Without a lot more information its difficult to know how to help

However, have a look at the CDate() function.
Oct 3 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
What's the SQL of your make-table query?
Oct 6 '07 #4

P: 37
I was able to obtain a soloution to this problem and just thought I would share it with the forum.
Expand|Select|Wrap|Line Numbers
  1. Sub AlterFieldType(TblName As String, FieldName As String, DataType As _
  2.    String, Optional Size As Variant)
  3. Dim cd
  4. If IsMissing(Size) Then
  5. DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" & FieldName & _
  6.    "] " & DataType
  7. Else
  8. DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" & FieldName & _
  9.    "] " & DataType & "(" & Size & ")"
  10. End If
  11. End Sub
  12.  
  13.  
  14. Public Function Update_GKNStatus()
  15. DoCmd.SetWarnings False
  16. Screen.MousePointer = 11
  17.  
  18. DoCmd.OpenQuery "qry-getdates"
  19. AlterFieldType "getdates", "status", "date"
  20. AlterFieldType "getdates", "line unit 9997", "date"
  21. AlterFieldType "getdates", "Line Unit 0002", "date"
  22. AlterFieldType "getdates", "line unit 9998", "date"
  23. AlterFieldType "getdates", "Line Unit 0003", "date"
  24. AlterFieldType "getdates", "Line Unit 0004", "date"
  25. AlterFieldType "getdates", "Line Unit 9901", "date"
  26. AlterFieldType "getdates", "Line Unit 0005", "date"
  27. AlterFieldType "getdates", "Line Unit 0006", "date"
  28. AlterFieldType "getdates", "Line Unit 0007", "date"
  29. AlterFieldType "getdates", "Line Unit 0008", "date"
  30. AlterFieldType "getdates", "Line Unit 0009", "date"
  31. AlterFieldType "getdates", "Line Unit 0010", "date"
  32.  
Oct 15 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 15 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.