Converting Data Types 2

[Sideline]

All

Does anyone know if it is possible to convert the data type of a text field to numeric with code. Using a create table query the destination field posesses the data type of the source field, being text. Using code I would like to convert the data type of the text field to numeric after the table has been created but can not find a solution in the help files. They only offer a create filed solution which allows one to specify the data type and size.

Yours Stumped

Richard

 

[jrbarnett]

I haven't been able to find a way of doing this, but a workaround is to create a new field of the type you want, use an update query and the CDbl/CDate/CLong type functions to convert data between types and copy it over, then drop the original field and rename it to the original.

John

 

[Sideline]

John,

As suggested I have used the CInt function in my build for the original make table query which produces a field of type number in the created table. Many thanks for the tip!

Richard

 

[mpastore]

You can also do it in code:

currentdb.Execute("alter table table1 alter column myid long&quot

 

[Sideline]

mpastore,

The following

CurrentDb.Execute ("Alter Table Deliverables alter column OutlineNumber long&quot

produces a 'syntax error in ALTER TABLE statement".

I cannot see where the error in the code is. Can you please debug?

Thanks

Richard

 

[mpastore]

That's strange. Worked fine for me, even create a table same as yours. How are you running the code? From the command window?

Hats off to Harper

 

[Sideline]

mpastore,

To clarify, which I should have earlier, a am trying to write a module that will delete a relationship, run a make table query, change the data type of one of the fields in the created table, recreate the relationship, run a select query that uses the created table and then preview a report twhich uses the select query.

Richard

ps who is harper?

 

[mpastore]

Can you send me what you have so far for code?

(Roy) Harper, I'm an old Led Zep fan

Hats off to Harper

 

[Sideline]

Dim dbs As Database, rel As Relation
Set dbs = CurrentDb
Dim tdf As TableDef, linktdf As TableDef
Dim fld As Field, fld1 As Field

Set linktdf = dbs.TableDefs("Task_Information&quot
linktdf.RefreshLink

'dbs.Execute ("Alter Table Deliverables alter column OutlineNumber long&quot

Set rel = dbs.Relations("DeliverablestoProjectMaster&quot
dbs.Relations.Delete rel.Name

DoCmd.SetWarnings False
DoCmd.OpenQuery "MilestonesbyProjects"
DoCmd.SetWarnings True

' Create new Relation object and specify foreign table.
Set rel = dbs.CreateRelation("DeliverablestoProjectMaster", "ProjectMaster", "Deliverables&quot
' Set attributes to enforce referential integrity.
rel.Attributes = dbRelationDontEnforce
' Create field in Relation object.
Set fld = rel.CreateField("OutlineNumber&quot
' Specify field name in foreign table.
fld.ForeignName = "OutlineNumber"
' Append Field object to Fields collection of Relation object.
rel.Fields.Append fld
' Append Relation object to Relations collection.

dbs.Relations.Append rel
dbs.Relations.Refresh

DoCmd.OpenQuery "DeliverablesStatusReportData", , acReadOnly
DoCmd.Close
DoCmd.OpenReport "DeliverablesStatusReport", acViewPreview

Set dbs = Nothing

As suggested earlier in the thread I have now changed the query to include the CInt function that forces the text field data type to be converted to number.

So in a way my problem has been solved but I am curious how to achieve the same result using code.

thanks again

Richard