Convert a data type using a macro

Posted on 2004-09-07
Medium Priority
Last Modified: 2010-05-18
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?

Question by:JamesCbury
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 34

Expert Comment

ID: 12001071
you can use a  querydef to do it

Update tblmyTable Set tblMyTable.myDate = #01/01/1901# where tblMyTable.myDaye = 'Never';
LVL 66

Expert Comment

by:Jim Horn
ID: 12001085
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.
LVL 41

Expert Comment

ID: 12001178
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 12009094
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.
LVL 41

Expert Comment

ID: 12009166
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.

Author Comment

ID: 12009578

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

LVL 41

Expert Comment

ID: 12009702
Can you post your function(s)?

Author Comment

ID: 12009916
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
LVL 41

Accepted Solution

shanesuebsahakarn earned 1400 total points
ID: 12010208
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"

Author Comment

ID: 12010268
Awsome... That works great

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question