formatting a calculated number field on a form

[Myrna Larson]

My database consists of medical information and includes, specifically, the
age at which certain events may have occured. I have written VBA macro to
calculate the age.

For calculating, for example, the age at death, the arguments are the date of
birth (DoB) and the date of death (DoD). If a patient is alive, DoD is null,
and the VBA function returns a value of 0. If the patient has died, the age is
calculated in decimal years, rounded to 2 decimal places, e.g. something like
78.32.

I have created a query with a field that calculates ages as described above.
The expression used on the query for age at death is

AgeAtDeath: Age(DoB,DoD)

and it is formatted as Fixed.

The query is the data source for a form. I would like to achieve the following
appearance on the form:

1. For a live patient, I see 0.00. I want it to look blank.
2. For a dead patient that lived exactly 69 years, I see 69 rather than 69.00.
For a patient that lived 38.2 years, I see 38.2 rather than 38.20.

I can get around those 2 issues by changing the expression on the form to
something like

Iif(Age(DoB,DoD)=0,"",Format$(Age(DoB,DoD),"0.00"))

But now the field on the form contains text rather than a number, so there are
problems filtering correctly. If, for example, I want to see only patients who
died after age 40, I type in the Filter-by-form box

40

and Access changes that to

"40"

The consequence is that patients who died between the ages of 5.00 and 9.99
are incorrectly included since the filter is comparing text rather than
numbers.

Is there any way to achieve the desired format ('blank' or 2 decimal places)
while still having the field data treated as a number for the purpose of
filtering?

 

[Myrna Larson]

Correction: I said "changing the expression on the form...". I should have
said "changing the expression in the query..."

 

[peregenem]

I have created a query with a field that calculates ages as described above.
The expression used on the query for age at death is

AgeAtDeath: Age(DoB,DoD)

and it is formatted as Fixed.

The query is the data source for a form. I would like to achieve the following
appearance on the form:

1. For a live patient, I see 0.00. I want it to look blank.
2. For a dead patient that lived exactly 69 years, I see 69 rather than 69.00.
For a patient that lived 38.2 years, I see 38.2 rather than 38.20.

I can get around those 2 issues by changing the expression on the form to
something like

Iif(Age(DoB,DoD)=0,"",Format$(Age(DoB,DoD),"0.00"))

But now the field on the form contains text rather than a number, so there are
problems filtering correctly. If, for example, I want to see only patients who
died after age 40, I type in the Filter-by-form box


and Access changes that to


The consequence is that patients who died between the ages of 5.00 and 9.99
are incorrectly included since the filter is comparing text rather than
numbers.

The principles of tiered architecture tells us to use the data layer to
return raw values and use the front end to apply any formatting. Access
tends to blur the lines between these boundaries e.g. a .mdb file is
referred to as and 'Access database' even though it contains front end
elements such as reports and forms.

Worse, I see evidence of users embedding hard coded references to front
end elements in the database layer e.g. a Query object (a.k.a. a VIEW),
which is very much a data layer element, containing a form's control in
the SQL where clause. Using a tiered approach, entirely possible in
Access, would be to create a Parameter Query object (a.k.a. a
PROCEDURE, stored proc, etc) in the data layer with strongly typed
parameters (possibly supplying default values); the front end would
then invoke the Query/proc passing values as required.

Why is this tiered approach not favored in Access? I really have no
idea. I get the impression that most Access font end developers choose
to have forms with bound controls: always bound, always connected, the
data is immediately committed as soon as a control's value is changed,
etc. What criteria do they use to make this choice: default behavior,
less/no programming required, lack of/no specific training in computing
science, quick kludge due to time/cost constraints? Again, I have no
idea.

To answer your question less obliquely, if you can't/won't change to a
tiered approach, how about having the Query/VIEW return two columns:
one being the raw data which is kept hidden from the user but available
to the front end developer under the hood in the form, and one column
formatted for display purposes only.

 

[Myrna Larson]

Are you suggesting that I should not use the built-in Filter-by-Form to allow
the user to set the filter criteria, but that I should develop my own form for
that purpose?

 

[peregenem]

Are you suggesting that I should not use the built-in Filter-by-Form to allow
the user to set the filter criteria, but that I should develop my own form for
that purpose?

One step at a time: you need the data layer elements before you can
build the front end elements (didn't you say the built in features
weren't working for you?)

You've given no details about your base tables, constraints, business
rules for age calculations, etc so I'll fake it with this test table:

CREATE TABLE Test (
key_col INTEGER NOT NULL UNIQUE,
DoB DATETIME NOT NULL,
DoD DATETIME
)
;
INSERT INTO Test (key_col, DoB, DoD)
VALUES (1, CDATE('1970-01-01'), CDATE('2005-11-01'))
;
INSERT INTO Test (key_col, DoB, DoD)
VALUES (2, CDATE('1940-02-02'), CDATE('2005-02-01'))
;
INSERT INTO Test (key_col, DoB, DoD)
VALUES (3, CDATE('1980-03-03'), NULL)
;

I'll fake the calculation to suit, too. My formatting should be as per
your spec, though. If you are not using 'ANSI SQL-92 query mode' or
ADO, you may find it quicker to paste the following SELECT into a new
Query object and save it manually:

CREATE VIEW TestView
AS
SELECT key_col,
IIF(DoD IS NULL, 0, DATEDIFF("d", DoB, DoD) / 365.25) AS age_at_death,
IIF(age_at_death = 0,
'',
FORMAT$(age_at_death,
IIF(RIGHT$(FORMAT$(age_at_death, '##0.00'), 2) = '00',
'##0',
'##0.00'
)
)
) AS display_age_at_death
FROM Test
;

Now we need another element in the data layer which will take a
strongly typed parameter for age (I assume you want to query on whole
years) and the applicable operators can be encoded. Again, if you want
to do this manually you can use a SELECT in a Query object using the
PARAMETERS declaration but you will have to forego the default
parameter value:

CREATE PROCEDURE TestProc (
:value INTEGER,
perator_code INTEGER = 1
) AS
SELECT key_col, age_at_death, display_age_at_death
FROM TestView
WHERE SWITCH(
age_at_death <= 0, 0,
perator_code = 1, IIF(age_at_death = :value, 1, 0),
perator_code = 2, IIF(age_at_death < :value, 1, 0),
perator_code = 3, IIF(age_at_death > :value, 1, 0),
perator_code = 4, IIF(age_at_death <= :value, 1, 0),
perator_code = 5, IIF(age_at_death >= :value, 1, 0),
perator_code = 6, IIF(age_at_death <> :value, 1, 0),
TRUE, 0) = 1
;

Now, to query on >40, use

EXEC TestProc 40, 3

With the required data layer elements in place, you can collect the
parameter values using forms and use them to call the proc/Parameter
Query.

 

[Myrna Larson]

This is an MDB database, not SQL. There is only 1 table (it's really just a
small flat-file which shouldn't require a relational DB, but see comment
below). There are just 700 patients.

Some persons have a genetically determined disease, others do not. Of the
affected persons, some have been treated with surgery, others have not. So
basically there are 3 groups: unaffected, affected but not treated, and
affected and treated.

We are tracking longevity and the occurrence and timing of heart attacks and
strokes in the 3 groups. I have created all of the queries using the QBE grid;
I haven't needed parameter queries. The form in question is used primarily for
data entry but on occasion I want to filter by age as well.

Each record contains data about a single patient, about 32 fields. There are 5
date/time fields: date of birth, date of surgery, date of heart attack or
stroke, date of last contact, and date of death. Any or all of these fields
can be null. I calculate 4 "ages": age at surgery, age at heart attack, age at
last contact, and age at death. For a living patient with no surgery and no
heart attack, only age at last contact is not null.

This data could easily be tracked as a flat-file in Excel, except for 3 Excel
problems: Excel can't handle dates prior to 1/1/1900 (some of these patients
were born in the 1800's), the user can fairly easily "scramble" the table by
improper sorting, and it's much more difficult to set up the "queries" and
filtering in Excel.

BUT... in Excel, I can easily create a data entry form and specify a number
format that (1) does not display zero values, (2) ALWAYS shows 2 decimal
places for non-zero values, and (3) achieving that format doesn't require
changing the type of the underlying data to text.

I don't seem to be able to do that in Access.

This is so EASY in Excel; I don't know why it should be so DIFFICULT
(impossible?) in Access. But I guess that's the way it is. Maybe the next
version of Excel will allow dates prior to 1/1/1900.

Anyway, thank you for your help.

PS: I have from a 25 year background in Basic programming and use of Excel. (I
was a VB and Excel MVP from 1994-2004.) But I am essentially a newbie when it
comes to Access.

 

[Myrna Larson]

FWIW, I have found the answer to my question. It's what is called a "custom
number format" in Excel.

In Access a custom format has 4 sections, separated by semicolons, which
specify the formats for for positive numbers, negative numbers, 0, and Null,
respectively.

To get the appearance I want on the form, I use this format specification, but
without the quotes:

"0.00; ; ; "

This solves my problem completely!

The reason I couldn't get it to work in the past was because I typed the
format string without the spaces between the semicolons (meaning 'don't
display', which is how you do it in Excel), but Access changed that to
"Fixed".

Before you ask why I didn't use Help for this, I did. I searched for "custom
number format" and got nothing. The way to find it is, on the Property sheet
for the control, select Format, and press F1. There they describe the 4
sections and comment that "If you use multiple sections but don't specify a
format for each section, entries for which there is no format will either
display nothing or default to the formatting of the first section."

If they had done the former -- display nothing -- I wouldn't have had a
problem. But it's solved now. Thanks for your help.

 

[peregenem]

This is an MDB database, not SQL. There is only 1 table

it's really just a
small flat-file which shouldn't require a relational DB

An .mdb is a flat file, that's why Access users talk terms of 'fields'
and 'records' rather than the relational terms 'columns' and 'rows'.
With Jet, however, you get a relational 'view' of the data in the file,
therefore an .mdb can be considered a DBMS (database management
system). Similarly, the .mdb can be considered a SQL because Jet
provides a SQL parser with which to query the data in the file; it fell
short of SQL-92 compliance but it's still pretty good and in some areas
even better than the SQL Server 2000 implementation (CASCADE paths and
CHECK constraints across multiple tables spring to mind). Speaking of
which, the SQL I posted was Jet SQL syntax and not that of SQL Server;
notice the parameter names: SQL Server forces you use a @ symbol
whereas Jet will let you use the Standard SQL colon character (so
that's another one in Jet's favour, then).

This is so EASY in Excel; I don't know why it should be so DIFFICULT
(impossible?) in Access.

I was a VB and Excel MVP from 1994-2004

Access is another (superfluous) layer on top of the .mdb and Jet. You
may find it helpful to use its GUI designers to build tables, VIEWs,
PROCEDURES. You certainly aren't compelled to use Access to build your
front end application; you could do this in Excel, in which you are
undoubtedly better skilled (I recognized the name, of course:
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/fedb13d97a74338c).
MS Access forms are quirky and unlike UserForms, Windows Forms and
every other forms engine I've encountered. They are a learning curve to
nowhere, IMHO.

This is really why I made the point about a tiered approach. Get the
database to do the things it does best i.e. manage data. I encourage
you to make your solution Access independent e.g. if you build your age
calculation in Access VBA and call it in an Access Query (VIEW) you
will not be able to use that Query/VIEW from Excel or any other front
end program; if the calculation can be build in Jet SQL then do so.

Maybe the next
version of Excel will allow dates prior to 1/1/1900.

I've been reading about the increase in some of the old limits
(http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx) but
haven't seen explicit mention of dates

 

[BruceM]

I have been watching this thread, but since I would have headed in rather a
different direction from that taken by peregenem I have sat back and
observed the exchange. I'm not sure why peregnem posts here, since he
clearly dislikes Access so much.
Access is designed to use bound controls. If you are opposed to the concept
of bound controls, Access is probably not the way to go.
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. DateDiff is the usual
way of calculating ages. The following, which borrows from some newsgroup
postings and from the Date/Time section of http://www.mvps.org/access/,
seems to work by 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])
You can sort and filter on that field. By the way, "Fixed" seems to be the
way the format "0.00" ends up. Same thing. Also, you can use Date()
instead of [DoD] to calculate a living person's age.
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.
SQL is (among other things) the language behind a query. You can choose SQL
View for a query to see how that looks. You can either enter the SQL
directly, or you can use the design view to build the SQL statement.
Parameter queries are the way Access provides for filtering data based on a
particular field.
I suspect you are frustrated because an Access table resembles a
spreadsheet, but it is very different. Both have their uses, but they are
not interchangeable. Your efforts seem to be centered on making an Access
table behave like an Excel spreadsheet. 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.

 

[Myrna Larson]

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.

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

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.

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.

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 spreadsheet using SQL queries that I contruct in the VBA code in the
spreadsheet. 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 suspect you are frustrated because an Access table resembles a
spreadsheet, 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
spreadsheet, and when to use which.

My question pertained to problems with a FORM, not a datasheet, and that form
doesn't resemble a spreadsheet in any way. (I need a form for data entry: I
have too many fields to do it in datasheet view, one of them is a memo field
of several hundred characters, including new lines, so editing them from
Datasheet 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 spreadsheet.

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

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.

 

[Myrna Larson]

Another point: When the format spec is "Fixed", it isn't always fixed. It
seems to mean NOT "2 decimal places" BUT "no more than 2 decimal places. Some
of my text boxes showed, e.g. 68 and another 38.2, when I expected them to
show 68.00 and 38.20.

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.

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

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.

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.

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 spreadsheet using SQL queries that I contruct in the VBA code in the
spreadsheet. 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 suspect you are frustrated because an Access table resembles a
spreadsheet, 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
spreadsheet, and when to use which.

My question pertained to problems with a FORM, not a datasheet, and that form
doesn't resemble a spreadsheet in any way. (I need a form for data entry: I
have too many fields to do it in datasheet view, one of them is a memo field
of several hundred characters, including new lines, so editing them from
Datasheet 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 spreadsheet.

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

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.

 

[Myrna Larson]

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.

I misspoke above. The result of DateDiff("yyyy",...) is 1, not 0.

When used with a first argument of "yyyy", the function seems to just subtract
the year portions of the two dates. This causes problems with time spans that
are less than a year, depending on whether the 2 dates are in the same or
different years.

If they are in the same year, say 1/1/2005 and 12/31/2005, the result is 0,
not 364/365 or .997. OTOH, if they are in different years, the result is 1,
even with a starting date of 12/31/2004 and ending date is 1/1/2005.

IOW, DateDiff("yyyy", ..." is, IMO, worthless.

 

[peregenem]

I'm not sure why peregnem posts here, since he
clearly dislikes Access so much.

What I *really* dislike is when Jet is passed off as Access or vice
versa. I have a genuine fondness for Jet. The day I disappear from
these ngs is the day they create a Jet newsgroup and the regulars here
start saying, 'This is a group for Access, the RAD forms-based,
data-centric application development platform, which enjoys continued
support from Microsoft. Your question appears to be about Jet, the
depreciated (five years and counting) data engine. Please re-post your
question to Microsoft.Public.Nostalgia.Jet...'

To be fair, I usually stick to what I know (you rarely see me post in
the Forms group and never in GettingStarted, right <g>?): I've (mainly)
posted a solution for the Jet side, here. Don't you think creating a
database which can only be used with one front end application is a
little short sighted? e.g. (down thread)

This renders the Age calculation unavailable outside Access. Use it in
the Query/VIEW and that Query/VIEW is useless outside of Access (hint:
you don't want Excel users in the base tables so give them a controlled
VIEW of the data...) I think this is worth pointing this out to an
obvious Excel aficionado.

 

[BruceM]

Comments inline.

I'll add my comments in-line below.


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.

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.


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 spreadsheet using SQL queries that I contruct in the VBA code in
the
spreadsheet. So I am quite familiar with SQL, though I wouldn't say I'm an
expert.


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.

No, that's not the issue. I understand the difference between a database
and a
spreadsheet, and when to use which.

My question pertained to problems with a FORM, not a datasheet, and that
form
doesn't resemble a spreadsheet in any way.

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

(I need a form for data entry: I
have too many fields to do it in datasheet view, one of them is a memo
field
of several hundred characters, including new lines, so editing them from
Datasheet view is not really an option. In addition, I have lots of error
checking in my BeforeUpdate event procedure.)


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


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.

 

[BruceM]

What I *really* dislike is when Jet is passed off as Access or vice
versa. I have a genuine fondness for Jet. The day I disappear from
these ngs is the day they create a Jet newsgroup and the regulars here
start saying, 'This is a group for Access, the RAD forms-based,
data-centric application development platform, which enjoys continued
support from Microsoft. Your question appears to be about Jet, the
depreciated (five years and counting) data engine. Please re-post your
question to Microsoft.Public.Nostalgia.Jet...'

I am largely self-taught. I don't really understand Jet. I can get Access
to do what I need. That's part of what what I get paid for.
I think the part that got to me was where you said that an mdb is a flat
file. When Excel is referred to as flat and Access as relational it is a
different sense of "flat" than it seems you are using to refer to an mdb
file.

To be fair, I usually stick to what I know (you rarely see me post in
the Forms group and never in GettingStarted, right <g>?): I've (mainly)
posted a solution for the Jet side, here. Don't you think creating a
database which can only be used with one front end application is a
little short sighted? e.g. (down thread)

No idea if it's short-sighted. I can create any number of front ends for a
single database, so I guess I am missing your point.

This renders the Age calculation unavailable outside Access. Use it in
the Query/VIEW and that Query/VIEW is useless outside of Access (hint:
you don't want Excel users in the base tables so give them a controlled
VIEW of the data...) I think this is worth pointing this out to an
obvious Excel aficionado.

I had missed the part about it being a VBA module.
Again, I don't see your point. I build databases to serve particular needs.
I don't see why I would want to use database elements such as calculations
elsewhere, or where "elsewhere" might be, for that matter. If I had that
need I may well re-examine my approach.

 

[peregenem]

I am largely self-taught. I don't really understand Jet.
I can get Access to do what I need.

This quote from the official MSDN Access12 blog
(http://blogs.msdn.com/access/) may clarify some points for you:

"Access has used the Microsoft Jet database engine for data storage and
query processing. Jet is commonly thought of as being part of Access
(or even as being "Access") but in reality it is a Windows system
component built by the SQL Server team. Jet is unique in Microsoft
because it provides both a data storage mechanism with a heterogeneous
query processor. This ability to run queries across a number of
different data stores is a key reason people use Access"

Oh, no. What's this I see...?

"we needed to extend the Jet engine, so took a "private" copy of
it, and have extended it for Office. This means Access 12 no longer
uses the system Jet engine, but is tightly bound to its own version...
Developers can still program against the Access engine, but since it
isn't part of the system any more, application users will need Access
on their machines. Developers targeting users without Access can
continue to use the Jet engine as they have for years or can move to
SQL Server Express."

Don't you see, BruceM? You've won, dear boy! You've won grand and
glorious jackpot!

Jet has finally been taken in-house by Access and soon two will become
one, just as everyone seemed to think they were all along. There is no
point in designing databases for use outside of Access nor any need to
write SQL in consideration for non-Access users of Jet because they
won't be any!

I choose SQL Server Express. And so it ends. I'm done here. And you did
it! *You* did it, BruceM! I knew you would, I just knew you would!

 

[Myrna Larson]

No, it isn't worth pointing that out. I KNOW this limits the query to use from
within Access, and that's ALL that I want. There is no intention to move this
project to any other database. As I said, it doesn't really deserve a
relational database treatment except for the Excel problems I noted.

 

[Myrna Larson]

you don't want Excel users in the base tables so give them a controlled

VIEW of the data...) I think this is worth pointing this out to an
obvious Excel aficionado.

This "app" is not being used from Excel, because Excel can't handle the
birthdates prior to Jan 1, 1900. If that were not an issue, please note that
Excel has a built-in data entry form, or I could develop one in Excel.

The only "user" is myself. I am entering new data via the form so as to
implement error checking.

Also, the filtering for reports is easier in Access.

 

[BruceM]

No, I don't really see, but it seems maybe Access has been a sort of GUI on
top of Jet, but the new version of Access has its own version of Jet. It
leads me to wonder what is to become of the developer's version of Access.
In the past it did not require Access on the user's machine. I can't make
out whether that will change or not. If so, the developer's version becomes
irrelevant.
Anyhow, am I to gather that you are packing up and heading out? I wasn't
trying to win (I didn't even know it was a contest), only to understand a
little bit better. I hardly ever knew what you were talking about, but I
usually read your postings when I came across them.

 

[Myrna Larson]

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.