Averaging with and if statement

[Jori]

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

 

[Linq Adams via AccessMonster.com]

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'")

 

[Jori via AccessMonster.com]

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.

 

[John W. Vinson]

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.

 

[Jori via AccessMonster.com]

Thanks so much! Both of your input was very helpful and worked!

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