Default field format

[Ann Scharpf]

Is there a way to set a default format for new query fields in a database? I
am fairly new to Access and recently completed a financial analysis project.
Nearly every field I created was a calculated field and appeared without any
formatting. It got to be REALLY tiresome to right click on every bloody
field to change the attributes to Currency.

I'd appreciate any info you can give me. Thanks.

Ann

 

[TomHinkle]

The best you can do is set the format at the table level... BUT if it's a
calculated field, I'm affraid you are stuck.

BUT since you're new to access, keep this in mind.. Access is a very good
tool for data storage an manipulation but it's much more detailed than excel.
Queries are not really supposed to be the intended tool for presenting data,
they are used to manipulate it. Ideally you would use a report for
presentation or 'push' the data to excel.

I know that's not what you wanted to hear, but maybe it will help !!

 

[Ann Scharpf]

Thanks, Tom.

I was afraid that was the answer. I gotta say, I really HATE the Access
report writer, so I avoided it like the plague while I was trying to complete
this project. I've gotten a bit better at it but I really think it's
user-hostile. I did end up exporting a bunch of data to Excel because I'm so
much faster at formatting in it. Plus it has a lot more formatting features
than Access seems to.

I just was hoping there was some cool feature that I hadn't come across when
I was looking.

 

[parkjv1]

Hello,

You can create a user inteface on a form. Place a button on it and
then place some code to import the data and then format the various
fields using DDL. For instance, whenever I import data, it's usually
in a DBase III or highter format. Many of the fields have a width of
255 but I may only want a width of 8 and format text vs numeric.


I will post some code tomorrow when I get into work.

 

[Tim Ferguson]

I was afraid that was the answer. I gotta say, I really HATE the
Access report writer, so I avoided it like the plague while I was
trying to complete this project. I've gotten a bit better at it but I
really think it's user-hostile.

There is a pay-off between user-friendliness and configurability. The
report designer will create a huge variety of different types of
documents, but that versatility comes with a comlpex user interface.

FWIW, the easiest way to achieve what you want would be to group-select
all the relevant text boxes and fix the Format property in one go.

I did end up exporting a bunch of
data to Excel because I'm so much faster at formatting in it. Plus it
has a lot more formatting features than Access seems to.

Arguable: it's much harder to map Excel object to real-life dimensions on
the paper. There is a long cycle of print-review -- redraw-columns
getting a decent paper output from Excel. The spreadsheet, of course,
would be much more flexible for any post-query analysis and summary etc
etc.

I just was hoping there was some cool feature that I hadn't come
across when I was looking.

As long as you don't need the numbers in the final destination (wherever
that may be), then you can always take charge of the formatting yourself:

SELECT FORMAT(SUM(FinalCost),"$00.00") AS FinalCostTotal,
FORMAT(AVG(TaxPaid), "$00,000.00") AS MeanTaxPaid,
etc etc

or even

SELECT CCUR(SomeMoneyAmount), etc etc

and I guess, without testing, that might even survive transfer to another
app like Word or Excel.

As a matter of interest, do you get on any better in this regard with
other report writers like Crystal or the dotNet data grid, etc?

B Wishes


Tim F

 

[parker.john]

Realize there is more than one way to skin a cat, so to speak.

I use the Data Definition Language to alter the formatting of fields in
my table.

I have a number of forms for the various projects that I work on. Each
project has similar requirements.

This is an example of Data Definition Language to alter a field in my
access table. This code is tied to an [event procedure] on click that
is the code behind a pushbutton.

From left to right, the item in the first set of brackes is the name of

the table, the item in the second set of brackets is the name of the
field in that table. The last portion CHAR(8) changes the width from
255 to 8.

DoCmd.RunSQL "ALTER TABLE [ASE_EPI_DATA] ALTER COLUMN [StudyID]
CHAR(8)"

For further reading you can go to the following URL, if you have any
questions or it isn't clear enough, just let me know.

http://www.devshed.com/c/a/MySQL/Data-Definition-Language-Part-1/4/

Tks,

John

 

[Tim Ferguson]

(e-mail address removed) wrote in @g47g2000cwa.googlegroups.com:

This is an example of Data Definition Language to alter a field in my
access table. This code is tied to an [event procedure] on click that
is the code behind a pushbutton.

Of all the ways to skin a cat, this one seems most akin to starting with
the intestines and working outwards... there must be more obscure and
dangerous methods of exporting data but I haven't come across them..

For further reading you can go to the following URL, if you have any
questions or it isn't clear enough, just let me know.

No thank you: I know what DDL is for; I just haven't seen it (ab)used like
this before.

B Wishes


Tim F