Formatting Table Fields With Macro

[David Gibson]

Hi,

Can anyone point me in the direction as to how I can alter the format
of some table fields using a macro.

The formatting I require is to make certain fields display numbers in
2 decimal places and some date field display the date in mmm-yy
format.

I am not altering the data type of the field just the displayed format
of the existing data type.

I do not have any great knowlegde of VB or SQL so thought using the
macro facilities may offer a solution.

Regards
Dave Gibson

 

[Steve Schapel]

Dave,

You can't do this with a macro related to the table design. But then,
there is normally no need to do so... The general principle is that
tables are for data storage, and are not generally seen during normal
database usage. Data entry/editing/viewing is done via Forms, which are
the objects designed for this purpose. In the properties of the
relevant controls on the form, you can control the formatting using such
properties as the Format property and the Decimal Places property. I am
not sure why you would need to alter these using a macro, but if you do,
it can be done using a SetValue action in the macro. Arguments for such
a SetValue action might be, for example...
Item: [NameOfYourDateField].[Format]
Expression: "mmm\-yy"

 

[David Gibson]

Steve,

Many thanks for taking the time to reply to my question.

The data in the database is all imported via a macro from a series of
excel workbooks where the dates and numbers have slightly different
formatting. There is no data entry via forms etc and no reports in the
database.

The database simply provides data from the tables to external sources.
The reason I wished to get some form of formatting done via a macro
was that it saves me going back to the tables after the data import
and setting all the field formats for the particular fields in
question. As the data is all imported via a single macro I thought it
might be handy if the same macro could do the formatting for me.

The data is imported once a day every day, 7 days a week so its a bit
of a pain manually setting the field formats. Perhaps I should brush
up on a bit of VB and use a module, perhaps even placed in the macro
as I am sure that would solve the problem. Trouble is I have limited
knowledge of VB although I can just about read/understand a module &
nervously edit one for minor changes I would not know where to start
to compile one.

Once again, many thanks for the guidance

Regards
Dave

 

[Steve Schapel]

Dave,

It is still a mystery why you want to manipulate the format of the data
in the table. Formatting only affects the appearance/presentation of
the data, it does not affect the data itself or its value, so I can't
see how the formatting will have any bearing on the uses you are putting
the data to. But here's an idea that might be worth looking into...
1. Set up tables in the database with the formatting required.
2. Regard the tables that the Excel data is imported into as being
temprary "holding" tables.
3. As part of the import macro, add OpenQuery actions to run Append
Queries to move the imported data from the import tables to the "real" ones.
Hope you understand the basic concept I am getting at here.