If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formatting a calculated number field on a form
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? |
#2
|
|||
|
|||
formatting a calculated number field on a form
Correction: I said "changing the expression on the form...". I should have
said "changing the expression in the query..." On Wed, 16 Nov 2005 23:22:24 -0600, Myrna Larson wrote: 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? |
#3
|
|||
|
|||
formatting a calculated number field on a form
Myrna Larson wrote: 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. 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. |
#4
|
|||
|
|||
formatting a calculated number field on a form
|
#5
|
|||
|
|||
formatting a calculated number field on a form
Myrna Larson wrote: 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. |
#7
|
|||
|
|||
formatting a calculated number field on a form
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. |
#8
|
|||
|
|||
formatting a calculated number field on a form
Myrna Larson wrote: 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/micro...db13d97a74338c). 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/...26/474258.aspx) but haven't seen explicit mention of dates |
#9
|
|||
|
|||
formatting a calculated number field on a form
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" wrote in message ... 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. On 18 Nov 2005 01:08:00 -0800, wrote: Myrna Larson wrote: 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. |
#10
|
|||
|
|||
formatting a calculated number field on a form
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Move feild entries from form to form using global variables | JackCGW | General Discussion | 11 | November 14th, 2005 05:22 AM |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
Requerying a pop up form to display in the main form | Jennifer P | Using Forms | 13 | April 5th, 2005 06:59 PM |
open a form through a subform in access 2000 | Tammy | Setting Up & Running Reports | 12 | October 22nd, 2004 02:43 PM |
Form Doesn't Go To New Record | Steve | New Users | 15 | May 16th, 2004 04:33 PM |