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

x
?
Solved

coverting column type using code

Posted on 2000-05-04
5
Medium Priority
?
240 Views
Last Modified: 2006-11-17
Is there any way to change the column type at run time using code?  I have a floating point column that I can convert to a currency type in design mode, however I want to do it at run time.  I am going to email a system to several users, they are going to fill in some information and it is going to import some data.  When it is done importing, I want it to automatically change the column name so I don't have to go over and do it for them.....is this possible?
0
Comment
Question by:ShaunMarion
[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
  • 3
5 Comments
 
LVL 10

Accepted Solution

by:
paasky earned 400 total points
ID: 2777145
Hello ShaunMarion,

It's not possible to alter table field type at runtime (if that was what you wanted). Only way to do that is that you create a temporary column in your table, then move data there, remove old field and rename temporary field to old field name. Quite complicated eh?

Why not create new column into your table or use conversion functions to modify data or if you just need to change the field name you can alias it to different name with query:

SELECT Tax As TaxCode,
       Amount as [Extended Amount]
FROM Table1;

Hope this helps,
Paasky
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2777755
Paasky is right.  To do this in code,

1) Create a new column,
2) Transfer the data with a query (the fastest way) or a loop,
3) Delete the original column,
4) Create a new column of the type you want the data to be,
5) Copy back the data,
6) Then delete the temp field.


It probably would be better to change the data type on the way into the table than to get the data into the table then change it...

Brian
0
 
LVL 10

Expert Comment

by:paasky
ID: 2777835
Here's the link to PAQ where you'll find code which uses above method to modify field type in runtime:

http://www1.experts-exchange.com/bin/Q.10325861

Paasky
0
 
LVL 31

Expert Comment

by:hnasr
ID: 2778853
Why do you want to change the column type in the table?

What about using a query to change the type of the required field.

You may create the query using code, using the querydef object.



dim myq as queryDef
set myq = CurrentDb.CreateQueryDef("query_1", "select CCur(ty.y1) as y1, ty.y2

query_1 can be set as the record source for a form or a report!

0
 
LVL 10

Expert Comment

by:paasky
ID: 2793790
Glad I was able to help you ShaunMarion. Good luck to your project!

Best regards,
paasky
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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, 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…

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