Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

coverting column type using code

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
ShaunMarion
Asked:
ShaunMarion
  • 3
1 Solution
 
paaskyCommented:
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
 
BrianWrenCommented:
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
 
paaskyCommented:
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
 
hnasrCommented:
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
 
paaskyCommented:
Glad I was able to help you ShaunMarion. Good luck to your project!

Best regards,
paasky
0
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.

Join & Write a Comment

Featured Post

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'.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now