DAvg Criteria Syntax

[tammyreichert99]

Hi,

I'm trying to use DAvg in a report to get an average quantity in
department. I would like it to pull that last five days' qtyindept,
however, for now, I'm just trying to get it to pull for the correct
date, workcenter, and line before I address that. I currently get an
average qtyindept for the entire database. Any suggestions?

=DAvg("[qtyindept]","[Shop Floor Metrics Table]","[Shop Floor Metrics
Table].date=[Report-Production Daily Query].date" And "[Shop Floor
Metrics Table].workcenter=[Report-Production Daily Query].workcenter"
And "[Shop Floor Metrics Table].line=[Report-Production Daily
Query].line")

 

[John Spencer]

=DAvg("[qtyindept]"
,"[Shop Floor Metrics Table]"
,"[date]=#" & [Report-Production Daily Query].date &
"# And workcenter= """ &
[Report-Production Daily Query].workcenter &
""" And line= """ &
[Report-Production Daily Query].line & """ ")

IF Line and WorkCenter are number fields then change the triple quotes
into one quote and drop the last single quote



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[tammyreichert99]

Hi John!

Thanks for responding! Line and Workcenter are not number fields,
however, when I tried this, it couldn't find my query - Report-
Production Daily Query. It prompts me when I run the report and I
get a $name?.

Tammy

 

[John Spencer]

My mistake.

What is the query that you are using for the report?

Do you have the values for WorkCenter and Line and Date displayed on
your report? IF so, what are the names of the controls that are
displaying the values?

Guessing that the values are on the report and are named the same as the
fields that they represent, the DAvg function might look like the following.

=DAvg("[qtyindept]"
,"[Shop Floor Metrics Table]"
,"[date]=#" & ME.date & "# And workcenter= """ & ME.workcenter & """ And
line= """ & Me.line & """ ")

That may blow up on you since Date and (probably) Line are reserved
words in Access and have specific meanings.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi John!

Thanks for responding! Line and Workcenter are not number fields,
however, when I tried this, it couldn't find my query - Report-
Production Daily Query. It prompts me when I run the report and I
get a $name?.

Tammy

=DAvg("[qtyindept]"
,"[Shop Floor Metrics Table]"
,"[date]=#" & [Report-Production Daily Query].date &
"# And workcenter= """ &
[Report-Production Daily Query].workcenter &
""" And line= """ &
[Report-Production Daily Query].line & """ ")

IF Line and WorkCenter are number fields then change the triple quotes
into one quote and drop the last single quote

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

And LINE is a reserved word. Try

=DAvg("[qtyindept]"
,"[Shop Floor Metrics Table]"
,"[date]=#" & Me.Controls("date") & "# And workcenter= """ &
Me.workcenter & """ And Line= """ & Me.Controls("Line") & """ ")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi John!

Thanks for responding! Line and Workcenter are not number fields,
however, when I tried this, it couldn't find my query - Report-
Production Daily Query. It prompts me when I run the report and I
get a $name?.

Tammy

=DAvg("[qtyindept]"
,"[Shop Floor Metrics Table]"
,"[date]=#" & [Report-Production Daily Query].date &
"# And workcenter= """ &
[Report-Production Daily Query].workcenter &
""" And line= """ &
[Report-Production Daily Query].line & """ ")

IF Line and WorkCenter are number fields then change the triple quotes
into one quote and drop the last single quote

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================