Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert a data type using a macro

Posted on 2004-09-07
10
Medium Priority
?
853 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
[X]
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
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 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.
-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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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
 

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 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"
0
 

Author Comment

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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 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