Convert a data type using a macro

Hi All,  
I'm using an Access data base to return all accounts that haven't been loggeg into in over 90 days.  The data is imported from a text file and comes in as text. (most have the last login time as m/d/yyyy h:mm am/pm, but some accounts come in as "Never")

So far I've been changing all the "Never"s to 1/1/1901 using an update query then I've been manually changing the data type in the table definition to date so that my query will run.  This works, and it includes all my "Never" accounts, which is good.

However, I am trying to automate the process.  I want to be able to import the data then just execute my query without having to manually manipulate the data types (this also means that I have to reset them back to text to import data again the next time around.)  And if possible I would prefer to leave everything as text so that I don't have to convert the "Never"s to dates.

Can I do this with a macro, or in VB?

Who is Participating?
shanesuebsahakarnConnect With a Mentor Commented:
Ah, pardon me. Try this instead of the tabledefs line:

CurrentDb.Execute "ALTER TABLE [Users Permissions Dump] ALTER COLUMN PswdLastSetTime TEXT"

to change it to text, and this to change it back:
CurrentDb.Execute "ALTER TABLE [Users Permissions Dump] ALTER COLUMN PswdLastSetTime DATETIME"
you can use a  querydef to do it

Update tblmyTable Set tblMyTable.myDate = #01/01/1901# where tblMyTable.myDaye = 'Never';
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
A better way would be to import this data into a table where all fields are text.  Then run an update query, changing all instances to "Never" to whatever you like.  Then, append all data from this table into your production table, with the data types you prefer.

In order to pull this off, you'll need to write an Import Spec (File: Get External Data, Import, select your file, then click the 'Advanced' button on the lower left, follow instructions, and make sure to hit 'Save As' and give it a name).  Then, in your Docmd.TransferText statement, specifically state the import spec name.

Hope this helps.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

I second jim's suggestion. That will stop you having to continually change data types.

If you *really* want to do it, you can change the data type from VBA by:

Change dbText to dbDate to change it to a date.
JamesCburyAuthor Commented:
I think that I want to change the fields rather than maintaining another table.  (When I pass this off to another user I want to make it look simple).  

I've set up a macro that will reset all the tables that I am using for a new import, but I can't get that line of code to work when I do a "RunCode" in the macro.  The error reads "Microsoft Access can't find the name 'dbText' you entered in the expression."

Can I run this line in a macro? or do I need to create a module.
You will need to create a function. You can write one like this:

Function ChangeToText()
End Function

You can call it from your macro with RunCode, and the argument ChangeToText(). Equally, you could do it all from VBA without using a macro at all. You'd need to do a similar thing to change the field back to a date.
JamesCburyAuthor Commented:

I've got everything set up to call the function.  but I keep getting a error "3219" - Invalid operation.

Can you post your function(s)?
JamesCburyAuthor Commented:
This function should clear out my tables and reset the PswdLastSetTime on the User Permissions Dump to text.  This sets up my tables for the next import.

Function Reset()
CurrentDb.Execute ("Delete From [File Sys Dump]")
CurrentDb.Execute ("Delete From [Rights Dump]")
CurrentDb.Execute ("Delete From [Services Dump]")
CurrentDb.Execute ("Delete From [Shares Dump]")
CurrentDb.Execute ("Delete From [User Permissions Dump]")
CurrentDb.TableDefs("User Permissions Dump").Fields("PswdLastSetTime").Type = dbText
End Function
JamesCburyAuthor Commented:
Awsome... That works great
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.