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 |
#11
|
|||
|
|||
formatting a calculated number field on a form
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. On Mon, 21 Nov 2005 18:09:26 -0600, Myrna Larson wrote: 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. |
#12
|
|||
|
|||
formatting a calculated number field on a form
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. |
#13
|
|||
|
|||
formatting a calculated number field on a form
BruceM wrote: 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) I'm not sure how (or if) you got the Age function to work at all in Access The VBA code is in a module in the MDB file. 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. |
#14
|
|||
|
|||
formatting a calculated number field on a form
Comments inline.
"Myrna Larson" wrote in message ... 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 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 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 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. 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.) 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. 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. |
#15
|
|||
|
|||
formatting a calculated number field on a form
wrote in message ups.com... BruceM wrote: 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...' 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. I'm not sure how (or if) you got the Age function to work at all in Access The VBA code is in a module in the MDB file. 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. |
#16
|
|||
|
|||
formatting a calculated number field on a form
BruceM wrote: 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! |
#18
|
|||
|
|||
formatting a calculated number field on a form
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. |
#19
|
|||
|
|||
formatting a calculated number field on a form
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. wrote in message oups.com... BruceM wrote: 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! |
#20
|
|||
|
|||
formatting a calculated number field on a form
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. |
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 |