Daily Average by quarter

  • Thread starter Thread starter Pat C
  • Start date Start date
P

Pat C

I have a field that I need to calculate a conditional running sum that resets each calendar quarter. I have a formula that I am working with but receive the message: "You may have entered an operand without an operator". The formula I have is:
=DAvg("[Strght Avg Abdn%] ","[Service Guarentee Daily QRY]","Date Between #" & DateAdd("d",Datediff("d",if("d",if(month[date]<4,dateserial(year[date],1,1),if(month[date]<7,dateserial(year[date],4,1),if(month[date]<10,dateserial(year[date],7,1),dateserial(year[date],10,1))),[Date]) & "# AND #" & [Date] & "#")
I am using in a report that is grouping by quarter. Thanks for any help


Submitted via EggHeadCafe - Software Developer Portal of Choice
XAML "Windows Send Error Report"
http://www.eggheadcafe.com/tutorial...9c6-0e6c5954f2af/xaml-windows-send-error.aspx
 
I'm not sure what all the nested If()s are for but you need to use IIf()
rather than If(). Also, you have expressions like "Month[date]" which I
assume should be "Month([date])".

If you really must use "date" as a field name, try to always wrap it inside
[]s. Date is the name of a function and should not be used as a field name.

If you explain your IIf() logic, maybe someone can simplify it for you.
 
I do have the "if" as "iif" Realized after I posted. I am also trying the following formula:
=DAvg("[Strght Avg Abdn%] ","[Service Guarentee Daily QRY]","Date Between #" & DateAdd("q",-1,[Date]) & "# AND #" & [Date] & "#"). I am trying to have this conditional running sum add daily (mon through Fri) through each quarter (jan, feb & march for example) and start over at the begining of the next quarter. The above does not stop the averaging at the quarter end and restart for the next quarter.



Duane Hookom wrote:

I am not sure what all the nested If()s are for but you need to use
04-Feb-10

I am not sure what all the nested If()s are for but you need to use IIf()
rather than If(). Also, you have expressions like "Month[date]" which I
assume should be "Month([date])".

If you really must use "date" as a field name, try to always wrap it inside
[]s. Date is the name of a function and should not be used as a field name.

If you explain your IIf() logic, maybe someone can simplify it for you.

--
Duane Hookom
Microsoft Access MVP


:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Emotions from Mothers of Autistic Children
http://www.eggheadcafe.com/tutorial...f1-b01f1659d6b6/emotions-from-mothers-of.aspx
 
I would try something like:
=DAvg("[Strght Avg Abdn%]","[Service Guarentee Daily QRY]","[Date] Between
#" & DateSerial(Year([Date]),((Month([Date])-1) \ 3) *3 +1,1) & "# AND #" &
[Date] & "#")
 
Tried with no luck. Either of the previous are not stopping the calc at the end of the quarter and starting over with the next quarter. Here is what I am trying to accomplish as a running average.
Mon 5%, Tues 9%, Wed 3%, Thurs 7% Fri 6% Mon 9%(starting data) and the report would return
Mon 5%, Tues 7%, Wed 5.7%, Thurs 6%, Fri 6%, Mon 6.5%. Tues day would average Mon & Tues, Wed would average Mon, Tues & Wed, Thurs would average Mon,Tues,Wed&Thrus and then Friday would be an average of Mon thru Fri. The next Mon would average Mon Thru Mon and so on. I need this to continue each day through the end of the quarter and start over with the next quarter. With the prior formula it is continuing without starting over at the next quarter.



Duane Hookom wrote:

I would try something like:=DAvg("[Strght Avg Abdn%]","[Service Guarentee
05-Feb-10

I would try something like
=DAvg("[Strght Avg Abdn%]","[Service Guarentee Daily QRY]","[Date] Betwee
[Date] & "#"

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Glycolic Peels to Combat Signs of Aging
http://www.eggheadcafe.com/tutorial...79-573f1e9d627b/glycolic-peels-to-combat.aspx
 
Try create a small user-defined function that returns the start of a quarter
(see below). Save the function in a module named "modDateFunctions". Then use
the function in your report like:

=DAvg("[Strght Avg Abdn%]","[Service Guarentee Daily QRY]","[Date] Between
#" & GetQuarterDate([Date],"B") & "# AND #" &
[Date] & "#")

Function GetQuarterDate(datDate As Date, strBE As String) As Date
'this function will return the starting or ending date _
of a quarter containing datDate
' datDate is the date
' strBE is either _
"B" for Beginning or _
"E" for Ending
Dim intMth As Integer
Dim intReturnDay As Integer
Dim intYr As Integer
intYr = Year(datDate)
intMth = Month(datDate)
Select Case strBE
Case "B"
intMth = intMth - (intMth - 1) Mod 3
GetQuarterDate = DateSerial(intYr, intMth, 1)
Case "E"
intMth = intMth - (intMth - 1) Mod 3 + 2
GetQuarterDate = DateSerial(intYr, intMth + 1, 0)
End Select
End Function
 
I have not worked much with modules and it has been sometime since I have used Access. Where would I need to reference the module?



Duane Hookom wrote:

Try create a small user-defined function that returns the start of a
08-Feb-10

Try create a small user-defined function that returns the start of a quarte
(see below). Save the function in a module named "modDateFunctions". Then us
the function in your report like

=DAvg("[Strght Avg Abdn%]","[Service Guarentee Daily QRY]","[Date] Betwee
[Date] & "#"

Function GetQuarterDate(datDate As Date, strBE As String) As Dat
'this function will return the starting or ending date
of a quarter containing datDat
' datDate is the dat
' strBE is either
"B" for Beginning or
"E" for Endin
Dim intMth As Intege
Dim intReturnDay As Intege
Dim intYr As Intege
intYr = Year(datDate
intMth = Month(datDate
Select Case strB
Case "B
intMth = intMth - (intMth - 1) Mod
GetQuarterDate = DateSerial(intYr, intMth, 1
Case "E
intMth = intMth - (intMth - 1) Mod 3 +
GetQuarterDate = DateSerial(intYr, intMth + 1, 0
End Selec
End Functio
-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Create Hidden SharePoint List for Site Owners
http://www.eggheadcafe.com/tutorial...60-917a85a41b08/create-hidden-sharepoint.aspx
 
Pat,

If you want your problem solved quickly and your database up and running,
contact me. I can get it all working for you. I provide help for Access,
Excel and Word applocations for a small fee.

Steve
(e-mail address removed)
 
I have not worked much with modules and it has been sometime since I have
used Access. Where would I need to reference the module?
Click to expand...




Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP
 
Pat C,

You didn't give us much to go on to help you... Did you create the module
as Duane stated in his post? What exactly are you trying to do?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Pat C> wrote in message I have not worked much with modules and it has been sometime since I have
used Access. Where would I need to reference the module?



Duane Hookom wrote:

Try create a small user-defined function that returns the start of a
08-Feb-10

Try create a small user-defined function that returns the start of a quarter
(see below). Save the function in a module named "modDateFunctions". Then
use
the function in your report like:

=DAvg("[Strght Avg Abdn%]","[Service Guarentee Daily QRY]","[Date] Between
[Date] & "#")

Function GetQuarterDate(datDate As Date, strBE As String) As Date
'this function will return the starting or ending date _
of a quarter containing datDate
' datDate is the date
' strBE is either _
"B" for Beginning or _
"E" for Ending
Dim intMth As Integer
Dim intReturnDay As Integer
Dim intYr As Integer
intYr = Year(datDate)
intMth = Month(datDate)
Select Case strBE
Case "B"
intMth = intMth - (intMth - 1) Mod 3
GetQuarterDate = DateSerial(intYr, intMth, 1)
Case "E"
intMth = intMth - (intMth - 1) Mod 3 + 2
GetQuarterDate = DateSerial(intYr, intMth + 1, 0)
End Select
End Function
--
Duane Hookom
Microsoft Access MVP


:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Create Hidden SharePoint List for Site Owners
http://www.eggheadcafe.com/tutorial...60-917a85a41b08/create-hidden-sharepoint.aspx
 
Back
Top