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
|
|||
|
|||
Averaging with and if statement
I'm trying to create an average from a table with multiple records in it that
I identified as "originated by". I only want my form to show the averages of certain records (originated by = continuous improvements"). Right now ALL the records are being averaged with the Control Source: =Avg( [NumberofDaystoClose]). I'm trying to add the criteria that only those records identified as "continuous improvement" records should be averaged. I don't know Access very well at all. I assume I need to add some type of If, or When statement to that expression (show me the average for only those records identified as continuous improvement - which is #2 on the originated by table. I'm also not sure if I type the name or number (unique identifier) into the expression). The goal is to show the average days to close these opening findings from this particular category of issues (continuous improvements). My original table contains corrective actions from multiple sources (inspections, accident investigations, etc.). |
#2
|
|||
|
|||
Averaging with and if statement
Never used Avg() and can't, in fact, get help on it. I use DAvg() which can
do what you want: =DAvg("[NumberofDaystoClose]", "Table or Query Name Here", "[Originated By] = 'continuous improvements'") -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#3
|
|||
|
|||
Averaging with and if statement
Linq Adams wrote:
Never used Avg() and can't, in fact, get help on it. I use DAvg() which can do what you want: =DAvg("[NumberofDaystoClose]", "Table or Query Name Here", "[Originated By] = 'continuous improvements'") Thank you! I still can't seem to get it to work though. Is all the punctuation correct? I don't quite understand where brackets go. Why doesn't the "table" and "continuous improvement" have brackets? Am I typing spaces or quotes (single/double) incorrectly? Here's what I typed exactly: =DAvg("[NumberofDaystoClose]","HSE Issues and Findings","[OriginatedBy]"='Cont Imp (CI)'") I also tried to change Cont Imp (CI) to 2 because that's the autonumber assigned to it and it's listed as a "number" in the table. I thought the extra parentheses were confusing the issue but that's how I labeled it in the table. The error says expression entered has an invalid string. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#4
|
|||
|
|||
Averaging with and if statement
On Sat, 14 Nov 2009 14:16:07 GMT, "Jori via AccessMonster.com" u56178@uwe
wrote: Linq Adams wrote: Never used Avg() and can't, in fact, get help on it. I use DAvg() which can do what you want: =DAvg("[NumberofDaystoClose]", "Table or Query Name Here", "[Originated By] = 'continuous improvements'") Thank you! I still can't seem to get it to work though. Is all the punctuation correct? I don't quite understand where brackets go. Why doesn't the "table" and "continuous improvement" have brackets? Am I typing spaces or quotes (single/double) incorrectly? Here's what I typed exactly: =DAvg("[NumberofDaystoClose]","HSE Issues and Findings","[OriginatedBy]"='Cont Imp (CI)'") I also tried to change Cont Imp (CI) to 2 because that's the autonumber assigned to it and it's listed as a "number" in the table. I thought the extra parentheses were confusing the issue but that's how I labeled it in the table. The error says expression entered has an invalid string. Any object name (table, field, query) *may* be delimited with square brackets. If the name contains blanks or special characters it MUST be delimited with square brackets. The third argument to a domain function must be a String which is a valid SQL WHERE clause without the word WHERE. In your example you have unbalanced quotes (three of them). The actual criterion must be enclosed in either ' or " quotemarks; you can put a ' mark inside a string delimited with " marks, but in order to put a " into a string delimited by ", you must double it up: e.g. "This is a "" doublequote" will be interpreted as This is a " doublequote Your expression should be =DAvg("[NumberofDaystoClose]","[HSE Issues and Findings"] , "[OriginatedBy]='Cont Imp (CI)'") The third argument becomes [OriginatedBy]='Cont Imp (CI)' which is indeed a valid WHERE clause, if OriginatedBy is a TEXT field. For future reference, Text fields need either " or ' as delimiters (use " if the string might contain an apoostrophe); Date fields use # as delimiter, e.g. [StartDate] = #1/1/2009#; Number fields use no delimiter at all. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Averaging with and if statement
Thanks so much! Both of your input was very helpful and worked!
John W. Vinson wrote: Never used Avg() and can't, in fact, get help on it. I use DAvg() which can do what you want: [quoted text clipped - 14 lines] extra parentheses were confusing the issue but that's how I labeled it in the table. The error says expression entered has an invalid string. Any object name (table, field, query) *may* be delimited with square brackets. If the name contains blanks or special characters it MUST be delimited with square brackets. The third argument to a domain function must be a String which is a valid SQL WHERE clause without the word WHERE. In your example you have unbalanced quotes (three of them). The actual criterion must be enclosed in either ' or " quotemarks; you can put a ' mark inside a string delimited with " marks, but in order to put a " into a string delimited by ", you must double it up: e.g. "This is a "" doublequote" will be interpreted as This is a " doublequote Your expression should be =DAvg("[NumberofDaystoClose]","[HSE Issues and Findings"] , "[OriginatedBy]='Cont Imp (CI)'") The third argument becomes [OriginatedBy]='Cont Imp (CI)' which is indeed a valid WHERE clause, if OriginatedBy is a TEXT field. For future reference, Text fields need either " or ' as delimiters (use " if the string might contain an apoostrophe); Date fields use # as delimiter, e.g. [StartDate] = #1/1/2009#; Number fields use no delimiter at all. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
Thread Tools | |
Display Modes | |
|
|