DAvg function returning wrong results (1 Viewer)

cpampas

Registered User.
Local time
Today, 00:03
Joined
Jul 23, 2012
Messages
218
Yet you are not using the non working days?
yes I am adding the non working days with the above Function tiraFS, on post 12,
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:03
Joined
Sep 21, 2011
Messages
14,048
No, all that does is calculate them, you have not included that value in the DAvg()?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:03
Joined
Feb 19, 2002
Messages
42,981
Here's a database with some useful date function. If you want to exclude holidays as well as weekends, populate the holidays table.
 

Attachments

  • UsefulDateFunctions20201124b.zip
    300.2 KB · Views: 74

cpampas

Registered User.
Local time
Today, 00:03
Joined
Jul 23, 2012
Messages
218
Gasman, I am including it like this :
Code:
Public Function media(dias As Integer) As Double

Dim sd As Integer, d As Date
d = DateAdd("d", -dias, Now())
sd = tiraFS(d)
dias = dias + sd

media = DAvg("[pLast]", "qryCurrent", "dataCot BETWEEN Date() AND DateAdd('d'," & -dias & ", Date())")


End Function


Public Function tiraFS(inicial As Date) As Integer
Dim n As Integer, a As Date

For a = inicial To Date
  If Weekday(a) = 7 Or Weekday(a) = 1 Then n = n + 1
Next
tiraFS = n
End Function

Pat,
Very usefull functions I am sure I will be using them, thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:03
Joined
Sep 21, 2011
Messages
14,048
And as shown previously, that davg is not going to work????😔
 

Minty

AWF VIP
Local time
Today, 07:03
Joined
Jul 26, 2013
Messages
10,355
You are obsessed with using Now() instead of Date() and I guarantee it will cause you issues.
As @Gasman has pointed out you can't use - Dias in your criteria, we already showed you how to correct that.
Code:
Public Function media(dias As Integer) As Double

Dim sd As Integer, d As Date
    d = DateAdd("d", -dias, Date())
    sd = tiraFS(d)
    dias = -1 * (dias + sd)

    media = DAvg("[pLast]", "qryCurrent", "dataCot BETWEEN Date() AND DateAdd('d'," & dias & ", Date())")


End Function
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:03
Joined
Feb 19, 2002
Messages
42,981
WHY do you keep using Now()? We've ALL told you not to. I even gave you a concrete example and yet your code STILL uses Now()!!!!!!!!!!!!!!!!!!
 

cpampas

Registered User.
Local time
Today, 00:03
Joined
Jul 23, 2012
Messages
218
WHY do you keep using Now()? We've ALL told you not to. I even gave you a concrete example and yet your code STILL uses Now()!!!!!!!!!!!!!!!!!!

Sorry my fault, I had already chaged it to date(), but I copy/pasted the wrong syntax from a previous text
 

Users who are viewing this thread

Top Bottom