By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,877 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Formatting more than one field at the same time (and dates as dates)

P: n/a
If I need to format how the content of a field is displayed, I can
click ALT + ENTER from design view, and specify the format, for
example, the number of decimal digits and so on.
Is there a way to apply the same kind of formatting to more than one
field at the same time? I tried selecting multiple fields, but if then
I click ALT + ENTER I don't have the option to choose formatting.

Also, how can I format dates as dates? I have a table with some dates,
that are properly recognized and formatted. I create a summary query
showing the average dates by certain fields. However, the average
appears as a number and I can find no way to format it as a date. The
only solution I could find was to export the table to Excel and format
it as a date there, but it clearly is not an effective solution...

Thank you in advance for your help!

Oct 9 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
A1: There is no problem with selecting several controls at once, and then
setting their Format property in the Properties box.

A2: See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
The article explains how to use CVDate() around the calculated field so JET
recognises it as a date. If you don't follow how to apply this, post your
SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<my********@googlemail.comwrote in message
news:11**********************@k79g2000hse.googlegr oups.com...
If I need to format how the content of a field is displayed, I can
click ALT + ENTER from design view, and specify the format, for
example, the number of decimal digits and so on.
Is there a way to apply the same kind of formatting to more than one
field at the same time? I tried selecting multiple fields, but if then
I click ALT + ENTER I don't have the option to choose formatting.

Also, how can I format dates as dates? I have a table with some dates,
that are properly recognized and formatted. I create a summary query
showing the average dates by certain fields. However, the average
appears as a number and I can find no way to format it as a date. The
only solution I could find was to export the table to Excel and format
it as a date there, but it clearly is not an effective solution...

Thank you in advance for your help!
Oct 9 '07 #2

P: n/a
On 9 Oct, 15:21, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
A1: There is no problem with selecting several controls at once, and then
setting their Format property in the Properties box.
I'm sorry, but I'm a novice to Access and don't quite follow you.
I'd like to format the way Access shows certain fields in tables and
queries (e.g. set the number of decimal digits displayed). To do this,
I open the table/query I want in design view, select a column, click
ALT+ENTER, and choose the format I want from "format" in the property
sheet which appears. However, if I select more than one column and
then click ALT+ENTER, the property sheet which appears is different
and no longer has the "format" option.
I'm sorry if this may seem banal but I really have no clue! Btw, is
that the only way to format a field?
A2: See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
The article explains how to use CVDate() around the calculated field so JET
recognises it as a date. If you don't follow how to apply this, post your
SQL statement.
It doesn't work :(
The date fields are correctly formatted, recognized and displayed as
dates. However, I need to create another table which summarizes the
average date by type of account; The SQL code is:

SELECT DISTINCTROW [Main - with L8M CashFlows].SegmentDescription,
Avg([Main - with L8M CashFlows].Default_Date) AS AvgOfDefault_Date
FROM [Main - with L8M CashFlows]
GROUP BY [Main - with L8M CashFlows].SegmentDescription;

The calculations are correct, but I can't format the averages as
dates. I tried with CDate:

SELECT DISTINCTROW [Main - with L8M CashFlows].SegmentDescription,
Avg(CDate([Default_Date])) AS ReformattedDate
FROM [Main - with L8M CashFlows]
GROUP BY [Main - with L8M CashFlows].SegmentDescription;

Thanks!
Oct 9 '07 #3

P: n/a
Ah: you're trying to do that in table design, not form design?

Don't bother. Leave the Format property in the table blank. Instead, create
a form where the data is entered. (You can use Datasheet view if you want it
to look like a table.) In form design view, you can select several controls
at once, and apply a format.

Think of the table as the bucket to hold the data, and the form as the
interface to find/enter/manage the data. Therefore you don't need to format
the data in the table itself. (And there is a bug in Access 2003 SP3 that
doesn't display some data correctly if the table field has a Format
applied.)

BTW, if you have lots of repeating fields (such as Jan, Feb, Mar, ...), you
have a spreasheet type design, not something that is suitable for a
database. You will need to break the table into 2: one for whatever the main
record is, and a related table where you have a *record* for each month's
value.

Did you use Date/Time fields in table design? Or Text fields? If they are
date fields, you will be able to fix the problem as suggested. If you're
still stuck, switch your query to SQL View (View menu in query design), copy
the SQL statement, and post it here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<my********@googlemail.comwrote in message
news:11**********************@50g2000hsm.googlegro ups.com...
On 9 Oct, 15:21, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>A1: There is no problem with selecting several controls at once, and then
setting their Format property in the Properties box.

I'm sorry, but I'm a novice to Access and don't quite follow you.
I'd like to format the way Access shows certain fields in tables and
queries (e.g. set the number of decimal digits displayed). To do this,
I open the table/query I want in design view, select a column, click
ALT+ENTER, and choose the format I want from "format" in the property
sheet which appears. However, if I select more than one column and
then click ALT+ENTER, the property sheet which appears is different
and no longer has the "format" option.
I'm sorry if this may seem banal but I really have no clue! Btw, is
that the only way to format a field?
>A2: See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
The article explains how to use CVDate() around the calculated field so
JET
recognises it as a date. If you don't follow how to apply this, post your
SQL statement.

It doesn't work :(
The date fields are correctly formatted, recognized and displayed as
dates. However, I need to create another table which summarizes the
average date by type of account; The SQL code is:

SELECT DISTINCTROW [Main - with L8M CashFlows].SegmentDescription,
Avg([Main - with L8M CashFlows].Default_Date) AS AvgOfDefault_Date
FROM [Main - with L8M CashFlows]
GROUP BY [Main - with L8M CashFlows].SegmentDescription;

The calculations are correct, but I can't format the averages as
dates. I tried with CDate:

SELECT DISTINCTROW [Main - with L8M CashFlows].SegmentDescription,
Avg(CDate([Default_Date])) AS ReformattedDate
FROM [Main - with L8M CashFlows]
GROUP BY [Main - with L8M CashFlows].SegmentDescription;

Thanks!
Oct 9 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.