thegameoflife
Programmer
Is it possible to change the Data Type of a field in VBA
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Changing Data Type
- Thread starter thegameoflife
- Start date
- Status
TudorSmith
Programmer
It is possible to convert the value of the field, from one Data Type to another, for coding purposes, but I have never actually change the table field value using code.
For instance, you may be working with an integer, but you need the result as a string so you would use the Cstr() function (Convert to string) like this:
Similarly, you may have a string value which you need to convert to a date value (Assuming the structure would be recognised as a date structure):
Is this what you were after?
birklea
birklea ~©¿©~ <><
I know what I know...don't presume that I know what I don't! Smithism!!!
For instance, you may be working with an integer, but you need the result as a string so you would use the Cstr() function (Convert to string) like this:
Code:
Dim strResult As String
strResult = Cstr(intValue)Similarly, you may have a string value which you need to convert to a date value (Assuming the structure would be recognised as a date structure):
Code:
Dim dDate As Date
dDate = CDate("05/06/2002")Is this what you were after?
birklea
birklea ~©¿©~ <><
I know what I know...don't presume that I know what I don't! Smithism!!!
LittleSmudge
Programmer
Do you REALLY want to change the data type of a FIELD ?
If so then the data already in the field will give you some limitations on what you can do. Changing from Integer to Long is okay but you'll potentially lose data changing the other way.
It might be a good idea to create a new field in the desired type & size etc. Then use VB to cycle through the records and do the conversion, handling problems on the fly. Then if all goes well, delete the old field and re-name the new one.
All of that can be done in VBA - Yes
.. .. but is that what you want?
G LS
If so then the data already in the field will give you some limitations on what you can do. Changing from Integer to Long is okay but you'll potentially lose data changing the other way.
It might be a good idea to create a new field in the desired type & size etc. Then use VB to cycle through the records and do the conversion, handling problems on the fly. Then if all goes well, delete the old field and re-name the new one.
All of that can be done in VBA - Yes
.. .. but is that what you want?
G LS
- Thread starter
- #4
thegameoflife
Programmer
I ended up using altertable.
That will change the DataType, say I wanted to change the format. How do you get to that level?
That will change the DataType, say I wanted to change the format. How do you get to that level?
If you're looking at keeping the data, changing only the data type, I've found that a method that works well is to copy the table(structure only). Then paste and give the new table a temp name. Then change the datatype of your fields. Then create an append query to copy the data from the original table (A)to the new table (B).
LittleSmudge
Programmer
WHY do you want to change the format?
Just set the format of the control on the form or report that displays the data. You then have the flexibility to adjust the format depending on context. And the control's Format property overrides the table's.
G LS
Just set the format of the control on the form or report that displays the data. You then have the flexibility to adjust the format depending on context. And the control's Format property overrides the table's.
G LS
- Status
Similar threads
