Re: formatting a calculated number field on a form

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

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.

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

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.

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

Relevant Pages

  • Re: formatting a calculated number field on a form
    ... > I only used age at death as an example. ... > and heart attack, age 40 and heart attack, surgery and death, etc. ... >>bit different in that a parameter query limits the recordset before it ... I assumed that you were calculating on ...
    (microsoft.public.access.forms)
  • Re: Macros to run update queries
    ... Thanks for the reply, however, calculating the age at the end of the ... The age field is in the table and needs to remain there as, ... query, it would mean the addition of around 100 queries to four ...
    (microsoft.public.access.macros)
  • Re: Calculated Field in a Table
    ... I calculate the value of the column based off of the Birthdate field? ... use an expression to calculate the age on all forms and reports, ... doing it as described in the query you only have to write the expression ... By calculating it on the fly ...
    (microsoft.public.access.tablesdbdesign)
  • Re: conditional formatting in form slows down calculations
    ... table to see all dates and a filtered query to see recent dates. ... endless calculating when the form opens. ... Once it finished the calculating, ... > CF (Conditional Formatting) is a weird beast, and it can cause the strange ...
    (microsoft.public.access.forms)
  • Re: query error
    ... "Jeff Boyce" wrote: ... The second query groups them by job number for one total time on job. ... you've already recorded start and stop times, the TaskTime value could be ... I have a specific job number that is not calculating ...
    (microsoft.public.access.queries)