Change properties of a field in Access 1

[proteome]

Is it possible to change the properties of a field using VB in MS Access. I have imported data into a table and I now want to change to properties of certain fields. I would like to change the data type of the field "Register" to a Number, Double, Fixed with 2 decimal places. Any help would be greatly appreciated. I can change the values of the properties using design view but I would prefer to change the values directly after import using code

 

[TonyJollans]

Hi proteome,

This can be a bit awkward prior to A2K, but if you have that you should be able to use ..

[blue]Docmd.RunSQL "ALTER TABLE [i]YourTable[/i] ALTER COLUMN Register Double"[/blue]

.. to change the data type. To change the decimal places, I think you have to go for the field properties ..

[blue]DbEngine(0)(0).Tabledefs("[i]YourTable[/i]").Fields("Register").Properties("decimalplaces") = 2[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[proteome]

you rock and congrads for your earlier recognition today!!

 

[proteome]

Tony,

When setting the property decimal places it comes back with an error, property not found?

 

[TonyJollans]

Hi proteome,

Sorry, I expected it to exist. It seems you must create it first. In a bit of a rush at the moment - look at the CreateProperty and Properties.Append methods of the Field object; I'll look in later and post full code if you're still stuck.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[proteome]

Tony,
If it would not be too much trouble could you please post the code.

 

[TonyJollans]

Hi proteome,

This is not as simple as I suggested. This code DOES add the DecimalPlaces Property to the Field, but it does NOT show up in the Table Design screen and I don't know why at the moment.

[blue]Dim db As DAO.Database
Dim fld as DAO.Field
Dim prop as DAO.Property

Set db = CurrentDb()
Set fld = db.TableDefs("[i]YourTable[/i]").Fields("Register")
Set prop = fld.CreateProperty("DecimaPlaces", dbInteger, 2)
fld.Properties.Append prop[/blue]

Doubles, however, do not have a limited number of decimal places so I'm not sure that, even if you get it 'right', it will actually make any difference. What is it that you actually want to achieve?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[proteome]

Thanks tony,

I tried something similar yesterday with the same result. When I copy results over using code it automatically defaults several of the fields to text whenever I want it to be of type number, double, fixed, 4 decimal places. The format as mentioned above is extremely important, but I am puzzled on how to change the field once a table has been created, the earlier fix

Docmd.RunSQL "ALTER TABLE YourTable ALTER COLUMN Register Double"

indeed does convert the data to type number double, but then I need to also have the field fixed with 4 decimal places. The code you have posted previous does compile and seem to work but it does not actually change the number of decimal places, my other question would also have to be how to create the field with fixed format. I tried manually using design view setting this data to see if you were correct with the idea of decimal places and it seems if you set the field to type double,fixed, 4 decimal places it works fine.

Any thoughts, because this subject seems to be somewhat fuzzy whenever I go to look for ideas you seem to be the only person who has lead me in a good direction, thanks!!!

 

[TonyJollans]

Hi proteome,

What exactly do you mean by

type number, double, fixed, 4 decimal places

A Double is a defined numeric format without any options. You can control how data in it are formatted on output - and even restrict the numbers which are allowed into it (with validation), but you cannot change what it is.

Can you create what you want manually (and are you sure it really is what you want?) - if so can you post how and we'll work out some code to do it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[proteome]

If you use design view in Access you can change a fields properties by changing the values in the drop down boxes.

Under design View:
Data Type = Number
Field Size = double
Format = Fixed
Decimal Places = 4

This changes the field or column to the appropriate format.
Eg.
2.5434
Let me know if there is anything further I can explain, I really appreciate the help!

Proteome

 

[proteome]

Tony,

Does this make any sense to you

http://ourworld.compuserve.com/homepages/attac-cg/AtblTip.htm#FORMAT

 

[TonyJollans]

Thanks proteome,

Yes, it makes sense, and it showed me where I was going wrong - I was using the wrong data type for the decimal places value. So now we have some code which should do the whole thing ..

[COLOR=blue]Dim db As DAO.Database
Dim fld as DAO.Field
Dim prop as DAO.Property

Docmd.RunSQL "ALTER TABLE [i]YourTable[/i] ALTER COLUMN Register Double"

Set db = CurrentDb()
Set fld = db.TableDefs("[i]YourTable[/i]").Fields("Register")

Set prop = fld.CreateProperty("Format", dbText, "Fixed")
fld.Properties.Append prop

Set prop = fld.CreateProperty("DecimaPlaces", [highlight]dbByte[/highlight], 2)
fld.Properties.Append prop[/color]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[proteome]

Good stuff I will try it, I actually created a new table and defined the fields as I went and then used a Select INTo statement, it works but rather lengthy