formatting a calculated number field on a form

[BruceM]

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.


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.

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.


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.

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".

 

[Myrna Larson]

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?

 

[BruceM]

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]

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.