convert text field to date/time field using VBA

[Micah68]

I need to use VBA to convert a field in an imported table from a text data type to a date/time data type. Any suggestions greatly are appreciated.

 

[pbaldy]

It would depend on the format the text is in. At it simplest, CDate() will convert the text to date.

Paul
MS Access MVP 2007/2008

 

[SkipVought]

Hi,

It depends on the string format of the date.

BEST: yyyy-mm-dd or yyyy/mm/dd These are UNAMBIGUOUS. Can confidently be converted via CDate or DateValue functions.

The other NUMERIC forms are mm-dd-yyyy or dd-mm-yyyy. The CONVERSION of either of these can be problimatic. It is best to MAP the year, month and day values into the DateSerial function using the MID function.

Date formats that contain TEXT have special challenges.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Micah68]

Thanks. The data always comes in like this: mm/dd/yyyy -- there is always 2 digits on the month and day, so that part is good. So far, all I've had to do is change the data type of the field to Date/Time in Table Design View of the table and all is well. (I haven't seen any bogus dates yet.) But I would like to progammatically change the data type of the field in the table with VBA right after the import is finished. I will play with the DateSerial function and see how it goes. I've used MID before. Any other suggestions are greatly appreciated.

 

[Micah68]

I used this calculated field in a query:

CalcDate:
iif([ImportedDate]="",Null,
DateSerial(Mid([ImportedDate],7,4),
(Mid([ImportedDate],1,2),
(Mid([ImportedDate],4,2)))

...and it worked. I still don't know how to programmatically change a field's data type using VBA, but I can base the rest of what I want to do in the Access database off the query with the calculated field CalcDate that changes the date text into true date data... so this works.

Thanks!!

 

[SkipVought]

"...right after the import is finished"

If you IMPORT this data properly, the TEXT can be converted to Date/Time values in the process - NO need for VBA post processing!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Micah68]

If I was manually importing the table, yes, I could adjust the data types as I go, but I need to keep this process automated. Right now the import happens through a macro TransferDatabase action which works slick -- no user interaction required. I just need a programmatic -- VBA -- way to change a field's data type after the table and data is already established in the database. Is this even possible through VBA? I don't think that question has been directly answered. Thanks.

 

[pbaldy]

This type of thing should work:

CurrentDb.Execute "ALTER TABLE YourTableName ALTER COLUMN YourFieldName DateTime"

Paul
MS Access MVP 2007/2008

 

[SkipVought]

Hmmmmmmmm?

At SOME point in the process you have to MANUALLY decide which field(s) get converted.

Pay me NOW or pay me LATER.

Six of one; half a dozen of t'other.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Micah68]

Skip, There is no manual decision here. I already know what field I need to convert. The trick is only how to convert a field to a new data type programmatically.

pbaldy - I think that's exactly what I was looking for. Thanks.

 

[SkipVought]

"I already know what field I need to convert"

THEN, your Import Specification can be SAVED and reused whenever you import.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Micah68]

But see my earlier posting -- "Right now the import happens through a macro TransferDatabase action which works slick -- no user interaction required." But this import process provides no options for modification.

 

[SkipVought]

If you were to MANUALLY perform, ONE TIME, the import, with the appropriate date conversions, and SAVE the import specification, then you could use that Import Spec in your "slick" (however, imperfect, in need of post processing, not too slick in my book) macro, NO USER INTERACTION REQUIRED!!!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Micah68]

ok, I'm with you, but I do not konw how to save an import specification under menu system when importing objects from one Access database to another (File|Get External Data|Import). In this case the Import Wizard doesn't start (which is where I believe I've seen the option to save the import spec before). Thanks.

 

[SkipVought]

it is right in the import wizard dialog, advanced or options or something.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Micah68]

Sorry, but for this specific type of import "the Import Wizard doesn't start"

But that's ok, I've already worked through this. Thanks all, for the help.

 

[SkipVought]

"...convert a field in an imported table from a text data type ..."

File > Get ecxternal Data > Import > [text files] > Import Text Wizard > [ADVANCED]

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Micah68]

Skip, Thanks, but see my two earlier postings -- I'm "from one Access database to another (File|Get External Data|Import)." Importing a table from one Access database to another does not start the wizard which does not allow me to save the import specification. Your idea had merit, but it just doesn't work with the type of import I'm doing. Enough please!

 

[TheAceMan1]

How are ya SkipVought . . .

Correct me if I'm wrong, but the [blue]TransferText[/blue] method is the only one having the [blue]SpecificationName[/blue] parameter! . . . [blue]Micah68[/blue] is using [blue]Transferdatabase[/blue].

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[SkipVought]

Yea, I missed the fact that this is a database table.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}