formatting a calculated number field on a form

"Myrna Larson" wrote in message
...
I made the apparently erroneous assumption that your database was not for
tracking information about newborns and infants. For anybody over the age
of one (actually, six months, I think) it will work.

I only used age at death as an example. I am also calculating other time
intervals, though not on the form. For example the interval between,
surgery
and heart attack, age 40 and heart attack, surgery and death, etc. I have
5
dates (birth, surgery, heart attack, last contact, death). I may want to
calculate the number of years between any two of those 5 dates.

Again, I made an assumption based on something in an earlier posting:
"We are tracking longevity and the occurrence and timing of heart attacks
and strokes"
The assumption was that there is a history for each patient, in which case
a
related table would have made sense. Your next paragraph makes it clear
that you are tracking only the first incident, which was not clear to me.

Yes, sorry I was not more explicit about the purpose of the project, but
none
of these details have anything to do with my problem (formatting the
number)
or its solution, so I didn't provide them.

I should have said "a way" rather than "the way". I think that they are a
bit different in that a parameter query limits the recordset before it
becomes the form's record source, while a filter can limit the number of
records (by selecting a value from a combo box, for instance), but then
you
can remove the limitation. I expect the explanation is a bit imprecise.
From what you have said I expect a filter would not be the best choice for
you needs.

Sorry. I said the exact opposite of what I intended, which was that a
filter may well be your best choice. However, you would not need to write
multiple parameter queries. You could allow for any parameter to be blank.
You could search for all heart attack incidents in a date range, or all
incidents of any sort in a date range, or all heart attacks regardless of
date, or whatever you choose.

Why not? I may want to change the filter on-the-fly during an editing
session,
to double check certain items and in doing so, see ONLY the relevant
records,
for example see only those records that were updated in 2005 and have had
a
heart attack, or edited in 2005 and have had a stroke. I don't think
writing a
new parameter query for each possible scenario is worth the effort, as
each of
these filters would be used only once or twice at most.

Since you were doing a calculation it did not occur to me that you were
trying to format a field. I assumed (again) that you were calculating on
the fly, and formatting for display purposes. Does this mean you have
stored the calculation in your table?

In a way, I am calculating on the fly. The data form uses a query as its
data
source, and the 4 ages (surgery, heart attack, contact, and death) that
are
displayed on the form are automatically updated when the dates are
modified.

When I hear "field" I tend to think of a table field. Apparently you are
applying the format to a query field. My usual approach is to use forms to
view and change data, so I don't make much use of formatting in queries. I
got stuck on that point.

In response to a follow-up posting you made, the DateDiff expression I
offered will work as needed. Somebody who dies the day before or after
their birthday will show an even number of years, with 00 to the right of
the decimal point, any time you are using decimal years and the person is
at
least a year old. The problem you anticipated (40.01 or 39.99) does not
seem to occur, at least not in my tests.

I don't need any help with calculating the age/time interval g. I've
been
programming in Basic since the 1970's, and problems relating to time
intervals, and calculations involving decimal years, happen to be one of
my
special interests.

I must admit that when I saw 'DateDiff("yyyy"' and the number 365.25 in
your
formula I dismissed it immediately. As I mentioned above, I am calculating
other intervals with this VBA procedure. Age at death was just one
example.
One might encounter an interval of only a few days when, say, a patient
has
surgery and suffers a heart attack 1 day later.

My first thought on reading the above, had I not followed the rest of this
thread, would have been that these are two separate incidents rather than
two fields in one record. Much of what I wrote earlier was centered on that
assumption, which I think is reasonable enough in the relational database
world.

Your solution began with the equivalent of this expression:

IIf(DateDiff("yyyy",[Date1],[Date2])=0

This will fail whenever the Date1 and Date2 are in the same calendar year:
the
expression will return 0. The biggest error would be the case of Date1 =
Jan 1
and Date2 = Dec 31. The correct result is 364/365 = 0.997, not 0.

Three decimal places offers a level of precision not possible with two
decimal places, of course. I suggested 365.25 only because with two decimal
places there is some imprecision anyhow. "yyyy" in the DateDiff expression
could have been "d".

Otherwise your formula does give a reasonable approximation when the dates
are
in different calendar years.

There are other business applications, say determining whether a person is
eligible for a fringe benefit that requires at least 1 year of service, in
which the rounding in undesirable. In such a case, I can imagine that one
would not want the entitlement to occur one or more days early, i.e. hire
date
of 2/26/2004 and current date of 2/24/2005. The formula returns 1.00, and
we
are actually 2 days short.

That doesn't happen to be an issue in my project, but because it CAN be an
issue, I never divide by 365.25. My method in the general case is to

1. calculate the difference in whole years from the starting date to the
anniversary date prior to (or on) the 2nd date

2. calculate the number of days from that prior anniversary date to the
2nd
date

3. determine the number of days between the prior anniversary date and
the
next anniversary date. That will be 365 or 366, never 365.25

4. divide the number obtained in step 2 by the number from step 3, and
add
that result to the number obtained in step 1, i.e.

whole years + (extradays/365 or 366)

5. round as appropriate

You chose to reference a VBA procedure in a query expression. You could
also have used VBA to place the calculated value onto the form, or any
number of other options. Your wanted to avoid an additional query field.
Had I understood that I would not have responded.

Yes, I know I could. But I set up the calculations in the query because
this
query is used as the basis of other queries. And I don't NEED another
query
field. I set it up exactly the way I wanted it. My problem, again, was
getting
the calculated result to format as wanted, not how to do the calculation,
or
where to put the formula, etc.

As I said, thanks for your thoughts.

When I hear "field" I tend to think of a table field. Apparently you are
applying the format to a query field. My usual approach is to use forms to
view and change data, so I don't make much use of formatting in queries. I
got stuck on that point.

No, I am not. I thought I had given the details before, but...

1. I have a table. It includes the 5 dates I mentioned. The table doesn't
include fields with the calculated ages.

2. I have query based on that table. In addition to all of the fields from the
table, it has 4 calculated fields that use this formula: Age(DoB, Date2),
where Date2 is one of the other 4 date fields. So the ages are calculated
on-the-fly when the query runs. They are not stored in the table.

3. I have a form based on that query, with a text box for each of the columns
in the query, including the calculated ages.

4. It's the text boxes on the form that I want to format.

As I mentioned several times before, even though the format in the QUERY is
set as "Fixed", the boxes on the form may display 68 or 38.2 instead of 68.00
or 38.20.

The same thing happens if, in the QUERY, I set the format as "0:00; ; ; ": I
DON'T see 2 decimal places on the FORM or a "blank" text box. IOW, the format
I entered in the query does not seem to carry forward to the form, even
though, AIR, Help implies that it should.

So I tried to change the format properties of the text boxes. There, even
though the format is "Fixed", I see the same variable number of decimal
places.

I've mentioned this earlier: fixed doesn't seem to mean 2 DECIMAL PLACES. It
seems to mean NO MORE THAN 2 DECIMAL PLACES (which in itself is bizarre
behavior, IMO). I believe this must be a bug.

The only way to get what I want is set the format property of each text box on
the form and to enter a space for the sections of the format string where I
want it to show nothing. If I do that, then Access doesn't discard my format
string and replace it with "Fixed".

So my question was/is as specified in the subject line. It has nothing to do
with how to calculate the age or whether to use a parameter query or
filtering.

Interstingly enough, given the length of this thread, the only messages that
"solve" the problem are the ones that I posted myself g.

Somehow we've gotten off on all of these irrelevant tangents such as whether I
know the difference between a spreadsheet and a database; whether I am trying
to turn Access into a spreadsheet; how to calculate the age; whether my VBA
solution is portable to, say, SQL server or some other DB; whether I should be
using a parameter query instead of filtering; whether I should be using VBA in
the code for the form to calculate the ages instead of calculating them in the
query, etc, etc, etc.

AIR, my comments re Excel was that formatting seemed to be much easier in
Excel than in Access, and that empty sections of format string are interpreted
differently in Access than Excel, and it's difficult to find the info on
custom number formats in Access Help.

Anyway, I think we've beat this "horse" to death. I managed to eventually
solve the problem myself. End of thread, no?

Curiously, when I applied the formatting in the query field (using the
Format function in the calculation), then bound a text box on a form to that
field, the formatting (two decimal places) carried through to the text box.
In fact, there seemed to be nothing I could do to the text box properties to
influence the formatting. Apparently the field formatting overrides the
control formatting. That is something I hadn't realized before now.
Perhaps Access handles formatting differently with a user-defined function
than with a built-in function.
Anyhow, glad to know the project worked out. Have a good Thanksgiving.

"Myrna Larson" wrote in message
...
When I hear "field" I tend to think of a table field. Apparently you are
applying the format to a query field. My usual approach is to use forms
to
view and change data, so I don't make much use of formatting in queries.
I
got stuck on that point.

No, I am not. I thought I had given the details before, but...

1. I have a table. It includes the 5 dates I mentioned. The table doesn't
include fields with the calculated ages.

2. I have query based on that table. In addition to all of the fields from
the
table, it has 4 calculated fields that use this formula: Age(DoB, Date2),
where Date2 is one of the other 4 date fields. So the ages are calculated
on-the-fly when the query runs. They are not stored in the table.

3. I have a form based on that query, with a text box for each of the
columns
in the query, including the calculated ages.

4. It's the text boxes on the form that I want to format.

As I mentioned several times before, even though the format in the QUERY
is
set as "Fixed", the boxes on the form may display 68 or 38.2 instead of
68.00
or 38.20.

The same thing happens if, in the QUERY, I set the format as "0:00; ; ; ":
I
DON'T see 2 decimal places on the FORM or a "blank" text box. IOW, the
format
I entered in the query does not seem to carry forward to the form, even
though, AIR, Help implies that it should.

So I tried to change the format properties of the text boxes. There, even
though the format is "Fixed", I see the same variable number of decimal
places.

I've mentioned this earlier: fixed doesn't seem to mean 2 DECIMAL PLACES.
It
seems to mean NO MORE THAN 2 DECIMAL PLACES (which in itself is bizarre
behavior, IMO). I believe this must be a bug.

The only way to get what I want is set the format property of each text
box on
the form and to enter a space for the sections of the format string where
I
want it to show nothing. If I do that, then Access doesn't discard my
format
string and replace it with "Fixed".

So my question was/is as specified in the subject line. It has nothing to
do
with how to calculate the age or whether to use a parameter query or
filtering.

Interstingly enough, given the length of this thread, the only messages
that
"solve" the problem are the ones that I posted myself g.

Somehow we've gotten off on all of these irrelevant tangents such as
whether I
know the difference between a spreadsheet and a database; whether I am
trying
to turn Access into a spreadsheet; how to calculate the age; whether my
VBA
solution is portable to, say, SQL server or some other DB; whether I
should be
using a parameter query instead of filtering; whether I should be using
VBA in
the code for the form to calculate the ages instead of calculating them in
the
query, etc, etc, etc.

AIR, my comments re Excel was that formatting seemed to be much easier in
Excel than in Access, and that empty sections of format string are
interpreted
differently in Access than Excel, and it's difficult to find the info on
custom number formats in Access Help.

Anyway, I think we've beat this "horse" to death. I managed to eventually
solve the problem myself. End of thread, no?

On Wed, 23 Nov 2005 14:56:59 -0500, "BruceM"
wrote:

there seemed to be nothing I could do to the text box properties to
influence the formatting. Apparently the field formatting overrides the
control formatting.

My experience is just the opposite. The field formatting is NOT carried
forward to the control formatting.

My file is saved in Access 2000 format. I don't know if that's relevant or
not.