DAvg function w/ date criteria ?

[Korski]

I'm currently attempting to use the "DAvg" function to report an average for
a given month of the current year.
A little background: The form I'm building looks similar to a calendar
with text boxes under each month's name label. I would like each text box to
calculate the average post-oven density(TotalPOD) for each given month of the
year. I'm stuck at the criteria part of the expression. What do I put in
the criteria to tell it to average all the [TotalPOD]'s with a [DateMolded]
for only January of the current year ?

My current expression looks like this,
Where [TotalPOD] is a calculated total for each test that comes from
Qryblockdenandflex and has a corrresponding [DateMolded] for each.

=DAvg("[TotalPOD]","Qryblockdenandflex","[DateMolded] {for the month of
January of the current year} ")

......but of course the criteria part is not all there,
Any suggestions would be greatly appreciated as my aspirin
bottle is running low.
Thanks, Adam

 

[John W. Vinson]

I'm currently attempting to use the "DAvg" function to report an average for
a given month of the current year.
A little background: The form I'm building looks similar to a calendar
with text boxes under each month's name label. I would like each text box to
calculate the average post-oven density(TotalPOD) for each given month of the
year. I'm stuck at the criteria part of the expression. What do I put in
the criteria to tell it to average all the [TotalPOD]'s with a [DateMolded]
for only January of the current year ?

My current expression looks like this,
Where [TotalPOD] is a calculated total for each test that comes from
Qryblockdenandflex and has a corrresponding [DateMolded] for each.

=DAvg("[TotalPOD]","Qryblockdenandflex","[DateMolded] {for the month of
January of the current year} ")

.....but of course the criteria part is not all there,
Any suggestions would be greatly appreciated as my aspirin
bottle is running low.
Thanks, Adam

You can use the DateSerial() function to generate a range of dates:

=DAvg("[TotalPOD]","Qryblockdenandflex","[DateMolded] >= #" &
DateSerial(Year(Date()), 1, 1) & "# AND [DateMolded] < #" &
DateSerial(Year(Date()), 2, 1) & "#")

The second argument of DateSerial is the month, so 1 for January, 2 for
February, 12 for December.

 

[Korski]

Thank You So Much !

Thanks John, I never would have figured that out......(no more
aspirin needed)

Thanks again, Adam

I'm currently attempting to use the "DAvg" function to report an average for
a given month of the current year.
A little background: The form I'm building looks similar to a calendar
with text boxes under each month's name label. I would like each text box to
calculate the average post-oven density(TotalPOD) for each given month of the
year. I'm stuck at the criteria part of the expression. What do I put in
the criteria to tell it to average all the [TotalPOD]'s with a [DateMolded]
for only January of the current year ?

My current expression looks like this,
Where [TotalPOD] is a calculated total for each test that comes from
Qryblockdenandflex and has a corrresponding [DateMolded] for each.

=DAvg("[TotalPOD]","Qryblockdenandflex","[DateMolded] {for the month of
January of the current year} ")

.....but of course the criteria part is not all there,
Any suggestions would be greatly appreciated as my aspirin
bottle is running low.
Thanks, Adam

You can use the DateSerial() function to generate a range of dates:

=DAvg("[TotalPOD]","Qryblockdenandflex","[DateMolded] >= #" &
DateSerial(Year(Date()), 1, 1) & "# AND [DateMolded] < #" &
DateSerial(Year(Date()), 2, 1) & "#")

The second argument of DateSerial is the month, so 1 for January, 2 for
February, 12 for December.