How to Change Field Properties

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DevDaniel - 30 Jul 2007 20:04 GMT

I have: A table with number fields.

I want: To change the format and number of decimal places for a specific
field, Col3.
e.g. Col3 is data type number (no specific properties). I want to
programmatically change the field properties to:
Format: 0.000%
Number of decimals: 1

Any suggestions?

Reply to this Message

John W. Vinson - 30 Jul 2007 21:42 GMT

>I have: A table with number fields.
>
[quoted text clipped - 6 lines]
>
>Any suggestions?

Well... don't.

Table field properties and formats aren't particularly useful.

You can and should have Forms and Reports to display the data in your table;
the controls on the Form or Report can have the desired properties.

Note also that the default Number datatype is Long Integer, and that integers
by definition are whole numbers, which will not allow you to store percent
values or decimal places. You'll need to use either a Decimal datatype or a
Single or Double float, or use a Currency datatype instead of Number.

John W. Vinson [MVP]

Reply to this Message

DevDaniel - 31 Jul 2007 14:28 GMT

> >I have: A table with number fields.
> >
[quoted text clipped - 20 lines]
>
> John W. Vinson [MVP]

Thanks for taking time to offer advice John. People on this site have been
very helpful.

Unfortunately, I need to change the field properties of the table.
- The process creates (on a standard run) 154,500 tables.
- Each cell in the table (18-90 cells/table) requires an independent
calculation.
- Each table must be exported separately.
- Users frequently add/delete/modify the fields and cell calculations
through a GUI.
- I cannot change these specifications/requirements.
- Access cannot support this many tables. If I create a single table then
delete it, I need to compact the database to avoid exceeding the 2 GB limit.
Compacting the database after deleting each table will slow the process.

My solution is to create one 'holding' table. The table is populated with
data, exported, and then emptied. The field properties change each time a new
set of data is loaded into the table, so I need code to make the changes
(particularly for Field Size, Format, and Number of Decimals).

Reply to this Message

Douglas J. Steele - 31 Jul 2007 15:55 GMT

> Thanks for taking time to offer advice John. People on this site have been
> very helpful.
[quoted text clipped - 17 lines]
> set of data is loaded into the table, so I need code to make the changes
> (particularly for Field Size, Format, and Number of Decimals).

Create a query on that table, and set the properties through the query.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message

DevDaniel - 31 Jul 2007 19:58 GMT

Thanks, Doug!

> > Thanks for taking time to offer advice John. People on this site have been
> > very helpful.
[quoted text clipped - 19 lines]
>
> Create a query on that table, and set the properties through the query.

Reply to this Message

John W. Vinson - 31 Jul 2007 21:24 GMT

>Thanks for taking time to offer advice John. People on this site have been
>very helpful.
[quoted text clipped - 3 lines]
>- Each cell in the table (18-90 cells/table) requires an independent
>calculation.

grump... spreadsheets have cells. tables have fields. Just jargon, I know, but
it's significant jargon.

>- Each table must be exported separately.

Not quite. Each SET OF DATA must be exported separately. It is just exactly as
easy to export a Query as it is to export a Table, has far less overhead, lets
you export calculated fields directly without needing to store them, etc.

>- Users frequently add/delete/modify the fields and cell calculations
>through a GUI.

If that GUI is setting up a MakeTable query then it could be setting up a
Select query somewhat more easily. If the users are going to calculate a field
value and then edit that value, then yes, you need to store the result in a
table; but if the users are just editing the expression then a query will work
just as well.

>- I cannot change these specifications/requirements.

Nobody is suggesting that you do so.

>- Access cannot support this many tables. If I create a single table then
>delete it, I need to compact the database to avoid exceeding the 2 GB limit.
[quoted text clipped - 4 lines]
>set of data is loaded into the table, so I need code to make the changes
>(particularly for Field Size, Format, and Number of Decimals).

Filling and emptying the holding table will bloat your database too, not quite
as fast as MakeTables but it will certainly do so. Creating Queries and
exporting them will do so MUCH more slowly (though with that many tables
you'll certainly get some bloat!). And you can set the Format property of
fields in a Query just as well as you can set the Format property of a Table;
or, if the data is just going to be exported and not edited, you can use the
Format() function to cast the result of the calculation into a text string of
whatever format you desire.

It seems that you're working on the basis that you must have the data stored,
formatted, and (I hope not!) sorted correctly in a Table in order to export
it. I'm just arguing against that assumption, because there *is* an
alternative, one which I would argue is a better alternative: exporting
directly from Queries.

John W. Vinson [MVP]

Reply to this Message