Re: formatting a calculated number field on a form

Comments inline.

"Myrna Larson" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7ic4o15pmmfvll630dlpt92ehfnvkr9l4i@xxxxxxxxxx
> I'll add my comments in-line below.
>
>>I'm not sure how (or if) you got the Age function to work at all in
>>Access,
>>since as far as I know it is not an Access function.
>
> A query in an MDB file CAN call a user-defined (i.e. VBA) function. I have
> been doing that since I started this project, using Access 2000. I've now
> upgraded to Access 2003.

I missed that it was a user-defined function. I should have been able to
figure it out, since obviously you got some sort of results. Without
knowing anything about the procedure it is a bit difficult to make any
suggestions about it. But it seems you have solved what was vexing you, so
that is irrelevant.
>
> How did I get it to work? The VBA code is in a module in the MDB file. The
> function name is Age and it returns a double. I created the query in
> Design
> View, using the grid. e.g. to calculate age at death, I use fields named
> DoB
> and DoD. In the Field row of the query, I wrote AgeAtDeath:Age(DoB,DoD).
>
>>calculating the number of days and dividing by 365.25.
>>There may be some minor imprecision in some cases, but you can decide if
>>that matters.
>
>>AgeAtDeath:
>>IIf(DateDiff("yyyy",[DoB],[DoD])=0,"",Format(DateDiff("d",[DoB],[DoD])/365.25,"Fixed"))
>>Simplest way to sort and filter may be to add another field to the query:
>>DateCriteria: DateDiff("yyyy",[DoB],[DoD])
>
> I guess you haven't read any of my messages in the Excel forums in
> response to
> formulas using 365.25 as the number of days in a year.

You're right, I haven't read them. There's no particular reason why I
would.

> Actually they may be
> better described as "harangues" than "responses" <g>.
>
> That formula is much more complicated than Age(Dob,Dod), I think you'll
> agree.
> And for a patient who was born in, say, 1 Nov 2004, and died on 1 Feb
> 2005,
> your first DateDiff will give a result of 0. I don't want 0, I want 0.25.
> And
> there will be errors when the 2nd date is very close to the anniversary of
> the
> birth date. The user will certainly "notice" if a patient was born on Jan
> 15,
> 1940, a heart attack occurred on Jan 15, 1989, and the age is shown as
> 48.99
> or 49.01.

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.
>
>>Also, you can use Date()
>>instead of [DoD] to calculate a living person's age.
>
> No, I can't. I must use the date on which we last contacted the patient.
> He/she could have died or had a heart attack, etc, since the last contact.

I was just offering that in case it would help.
>
>>Access serves a very different purpose than Excel. If you have multiple
>>incidents for a single patient, is your approach to enter another complete
>>record? If so, you are taking the long way around to avoid using the very
>>aspect of Access that makes it a useful. The best way to do what you need
>>is to have a Patients table with a record for each patient, and a related
>>Incidents table to keep track of each individual incident. The only real
>>way of entering multiple incidents for one patient in Excel is to enter
>>the
>>patient information over and over. In Access you enter the patient
>>information once, and relate other information to that record.
>
> I do understand the concept of a normalized data structure. I believe I
> have
> it in my file. There is no repeated information from one record to the
> next.

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.
>
> I am only interested in the date and type of the FIRST incident. The
> patient
> is removed from the study at that point. We are, however keeping track of
> the
> count of heart attacks, I have a field for the date of the 1st event, what
> it
> was (heart attack, stroke, etc), plus 4 numeric fields for the counts of
> the 4
> kinds of problems we are tracking (heart attack, stroke, coronary artery
> and
> carotid artery surgery).
>
> I mentioned in an earlier post the problems with Excel that require me to
> use
> Access.
>
>>SQL is (among other things) the language behind a query.
>
> Yes, I know that, and I have used it. When editing an SQL statement, I
> often
> paste it into my editor program, where search and replace is available. In
> another application, I use stock prices stored in an MDB file. I import
> those
> into a spread*** using SQL queries that I contruct in the VBA code in
> the
> spread***. So I am quite familiar with SQL, though I wouldn't say I'm an
> expert.
>
>>Parameter queries are the way Access provides for filtering data based on
>>a
>>particular field.
>
> Is that what is happening "behind the scenes" when you use Filter-by-Form
> with
> a Form? Or is the Filter property of the record set being changed?

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.
>
>>I suspect you are frustrated because an Access table resembles a
>>spread***, but it is very different. Both have their uses, but they are
>>not interchangeable.
>
> No, that's not the issue. I understand the difference between a database
> and a
> spread***, and when to use which.
>
> My question pertained to problems with a FORM, not a data***, and that
> form
> doesn't resemble a spread*** in any way.

Terminology can be confusing, as there is a data*** view of a form as
well as a data*** view of a table or query.

> (I need a form for data entry: I
> have too many fields to do it in data*** view, one of them is a memo
> field
> of several hundred characters, including new lines, so editing them from
> Data*** view is not really an option. In addition, I have lots of error
> checking in my BeforeUpdate event procedure.)
>
>>Your efforts seem to be centered on making an Access
>>table behave like an Excel spread***.
>
> Not at all! I just want control over the *display format* of a number
> that's
> shown in a text box on a form. I've read messages here that say you can't
> really apply formatting to a data*** view of a table or query -- that
> has to
> be done in the form or report. And that's exactly what I have been trying
> to
> accomplish: the format on the FORM.
>
> I always want 2 decimal places, or when the result is 0, an "apparently"
> blank
> cell. And I want to accomplish this WITHOUT changing the date type of the
> underlying data from numeric to text or variant, so I can still filter on
> this
> as a numeric field. And there IS a way to do all of that without an
> additional
> field.
>
> I guess you didn't see my last post, where I said I had solved the
> problem.
> The answer is in the Format property for the field.
>

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?

> The problem I was having was that I entered this as the format:
>
> 0.00;;;
>
> and Access changed that to a specification with only 1 section, i.e.
>
> Fixed
>
> My fundamental problem was not being able to find Help for "custom number
> formats". Even though Help uses that term, the file is not indexed on it.
> You
> have to search for help on "Format Property". The 2nd problem is that the
> number format behaves a bit differently in Access than Excel, and without
> Help, I didn't know that. The solution was to type the format string as
>
> 0.00; ; ; ;
>
> Quite straightforward, once you know the "trick".
>
>>It isn't going to happen, but maybe
>>the folks in this group could help you get the most out of Access by
>>making
>>use of its capabilities.
>
> I think I am getting what I need from Access now that this latest
> irritation
> has been resolved.
>
> Thanks anyway for your thoughts.

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


.

Loading