Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Houston
    Posts
    2

    Question Unanswered: Change data type with VBA

    I need to automatically change the data type of several fields after they have been updated.

    The fields come in from an Excel sheet as "DDMMMYY" like this: "27JUN03" or "01JUL03".

    I have a SQL command (DoCmd.RunSQL ...) that adds dashes if the field isn't blank for that record, yeiding results like this: "27-JUN-03" or "01-JUN-03".

    Now I need to change the data types for those fields to Date/Time. I know it can be done in the design view, but I don't want the user having to do this. Is there a way to do this with VBA code? I don't mind if it's complicated, or if I should do something different.

    edit: This code doesn't work:
    Code:
    CurrentDb.TableDefs("myTable").Fields("someDate").Type = dbDate
    Last edited by SPBesui; 07-01-03 at 16:37.

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Personally, with these sort of import routines, I'd first read them into a 'Raw' import table and then either creat a view with them converted to dates or write them to a 'Clean' import table in the corect formats.


    Otherewise you will be changing the table design each time you run the import routine.

  3. #3
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    I have this sort of problem also, I format the cells in excel before imnporting them (use the custom format option) and I also tend to save the excel sheet as a csv format as there is less hassle on import.

  4. #4
    Join Date
    Jul 2003
    Location
    Houston
    Posts
    2
    Originally posted by Risky
    Personally, with these sort of import routines, I'd first read them into a 'Raw' import table and then either creat a view with them converted to dates or write them to a 'Clean' import table in the corect formats.

    Otherewise you will be changing the table design each time you run the import routine.
    Yeah, that's what I ended up doing. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •