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 |
#21
|
|||
|
|||
formatting a calculated number field on a form
"Myrna Larson" wrote in message ... 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. Sorry. I said the exact opposite of what I intended, which was that a filter may well be your best choice. However, you would not need to write multiple parameter queries. You could allow for any parameter to be blank. You could search for all heart attack incidents in a date range, or all incidents of any sort in a date range, or all heart attacks regardless of date, or whatever you choose. 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. When I hear "field" I tend to think of a table field. Apparently you are applying the format to a query field. My usual approach is to use forms to view and change data, so I don't make much use of formatting in queries. I got stuck on that point. 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. My first thought on reading the above, had I not followed the rest of this thread, would have been that these are two separate incidents rather than two fields in one record. Much of what I wrote earlier was centered on that assumption, which I think is reasonable enough in the relational database world. 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. Three decimal places offers a level of precision not possible with two decimal places, of course. I suggested 365.25 only because with two decimal places there is some imprecision anyhow. "yyyy" in the DateDiff expression could have been "d". 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. |
#22
|
|||
|
|||
formatting a calculated number field on a form
When I hear "field" I tend to think of a table field. Apparently you are
applying the format to a query field. My usual approach is to use forms to view and change data, so I don't make much use of formatting in queries. I got stuck on that point. No, I am not. I thought I had given the details before, but... 1. I have a table. It includes the 5 dates I mentioned. The table doesn't include fields with the calculated ages. 2. I have query based on that table. In addition to all of the fields from the table, it has 4 calculated fields that use this formula: Age(DoB, Date2), where Date2 is one of the other 4 date fields. So the ages are calculated on-the-fly when the query runs. They are not stored in the table. 3. I have a form based on that query, with a text box for each of the columns in the query, including the calculated ages. 4. It's the text boxes on the form that I want to format. As I mentioned several times before, even though the format in the QUERY is set as "Fixed", the boxes on the form may display 68 or 38.2 instead of 68.00 or 38.20. The same thing happens if, in the QUERY, I set the format as "0:00; ; ; ": I DON'T see 2 decimal places on the FORM or a "blank" text box. IOW, the format I entered in the query does not seem to carry forward to the form, even though, AIR, Help implies that it should. So I tried to change the format properties of the text boxes. There, even though the format is "Fixed", I see the same variable number of decimal places. I've mentioned this earlier: fixed doesn't seem to mean 2 DECIMAL PLACES. It seems to mean NO MORE THAN 2 DECIMAL PLACES (which in itself is bizarre behavior, IMO). I believe this must be a bug. The only way to get what I want is set the format property of each text box on the form and to enter a space for the sections of the format string where I want it to show nothing. If I do that, then Access doesn't discard my format string and replace it with "Fixed". So my question was/is as specified in the subject line. It has nothing to do with how to calculate the age or whether to use a parameter query or filtering. Interstingly enough, given the length of this thread, the only messages that "solve" the problem are the ones that I posted myself g. Somehow we've gotten off on all of these irrelevant tangents such as whether I know the difference between a spreadsheet and a database; whether I am trying to turn Access into a spreadsheet; how to calculate the age; whether my VBA solution is portable to, say, SQL server or some other DB; whether I should be using a parameter query instead of filtering; whether I should be using VBA in the code for the form to calculate the ages instead of calculating them in the query, etc, etc, etc. AIR, my comments re Excel was that formatting seemed to be much easier in Excel than in Access, and that empty sections of format string are interpreted differently in Access than Excel, and it's difficult to find the info on custom number formats in Access Help. Anyway, I think we've beat this "horse" to death. I managed to eventually solve the problem myself. End of thread, no? |
#23
|
|||
|
|||
formatting a calculated number field on a form
Curiously, when I applied the formatting in the query field (using the
Format function in the calculation), then bound a text box on a form to that field, the formatting (two decimal places) carried through to the text box. In fact, there seemed to be nothing I could do to the text box properties to influence the formatting. Apparently the field formatting overrides the control formatting. That is something I hadn't realized before now. Perhaps Access handles formatting differently with a user-defined function than with a built-in function. Anyhow, glad to know the project worked out. Have a good Thanksgiving. "Myrna Larson" wrote in message ... When I hear "field" I tend to think of a table field. Apparently you are applying the format to a query field. My usual approach is to use forms to view and change data, so I don't make much use of formatting in queries. I got stuck on that point. No, I am not. I thought I had given the details before, but... 1. I have a table. It includes the 5 dates I mentioned. The table doesn't include fields with the calculated ages. 2. I have query based on that table. In addition to all of the fields from the table, it has 4 calculated fields that use this formula: Age(DoB, Date2), where Date2 is one of the other 4 date fields. So the ages are calculated on-the-fly when the query runs. They are not stored in the table. 3. I have a form based on that query, with a text box for each of the columns in the query, including the calculated ages. 4. It's the text boxes on the form that I want to format. As I mentioned several times before, even though the format in the QUERY is set as "Fixed", the boxes on the form may display 68 or 38.2 instead of 68.00 or 38.20. The same thing happens if, in the QUERY, I set the format as "0:00; ; ; ": I DON'T see 2 decimal places on the FORM or a "blank" text box. IOW, the format I entered in the query does not seem to carry forward to the form, even though, AIR, Help implies that it should. So I tried to change the format properties of the text boxes. There, even though the format is "Fixed", I see the same variable number of decimal places. I've mentioned this earlier: fixed doesn't seem to mean 2 DECIMAL PLACES. It seems to mean NO MORE THAN 2 DECIMAL PLACES (which in itself is bizarre behavior, IMO). I believe this must be a bug. The only way to get what I want is set the format property of each text box on the form and to enter a space for the sections of the format string where I want it to show nothing. If I do that, then Access doesn't discard my format string and replace it with "Fixed". So my question was/is as specified in the subject line. It has nothing to do with how to calculate the age or whether to use a parameter query or filtering. Interstingly enough, given the length of this thread, the only messages that "solve" the problem are the ones that I posted myself g. Somehow we've gotten off on all of these irrelevant tangents such as whether I know the difference between a spreadsheet and a database; whether I am trying to turn Access into a spreadsheet; how to calculate the age; whether my VBA solution is portable to, say, SQL server or some other DB; whether I should be using a parameter query instead of filtering; whether I should be using VBA in the code for the form to calculate the ages instead of calculating them in the query, etc, etc, etc. AIR, my comments re Excel was that formatting seemed to be much easier in Excel than in Access, and that empty sections of format string are interpreted differently in Access than Excel, and it's difficult to find the info on custom number formats in Access Help. Anyway, I think we've beat this "horse" to death. I managed to eventually solve the problem myself. End of thread, no? |
#24
|
|||
|
|||
formatting a calculated number field on a form
On Wed, 23 Nov 2005 14:56:59 -0500, "BruceM"
wrote: there seemed to be nothing I could do to the text box properties to influence the formatting. Apparently the field formatting overrides the control formatting. My experience is just the opposite. The field formatting is NOT carried forward to the control formatting. My file is saved in Access 2000 format. I don't know if that's relevant or not. |
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 |