Solved

Convert a data type using a macro

Posted on 2004-09-07
10
845 Views
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?

Thanks
0
Comment
Question by:JamesCbury
10 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12001071
you can use a  querydef to do it

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

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.
-Jim
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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:
CurrentDb.TableDefs("MyTable").Fields("MyField").Type=dbText

Change dbText to dbDate to change it to a date.
0
 

Author Comment

by:JamesCbury
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12009166
You will need to create a function. You can write one like this:

Function ChangeToText()
CurrentDb.TableDefs("MyTable").Fields("MyField").Type=dbText
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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:JamesCbury
ID: 12009578
Thanks,

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

0
 
LVL 41

Expert Comment

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

Author Comment

by:JamesCbury
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
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 350 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"
0
 

Author Comment

by:JamesCbury
ID: 12010268
Awsome... That works great
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

914 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now