DAvg function returning wrong results (1 Viewer)

[cpampas]

Hello,
I have been struggling with this function wich returns the wrong result (25,25), when I should get (23,32) :

DAvg("[pLast]", "qryCurrent", "dataCot BETWEEN #" & Now() & "# AND #" & DateAdd("d", -10, Now()) & "#")

if i paste the date parameter ( BETWEEN #04/10/2021 09:10:45# AND #19/09/2021 09:10:45# )into the query i get the following results:

ID	pLast	dataCot
24148	23,92	20-set-21
24147	23,92	21-set-21
24146	23,67	22-set-21
24145	23,54	23-set-21
24144	23,44	24-set-21
30318	23,45	27-set-21
30317	23,059999	28-set-21
30316	22,94	29-set-21
30315	22,829999	30-set-21
30314	22,899999	01-out-21

i also noticed that a count of records does not return 10 records as it should
?Dcount("[pLast]", "qryCurrent", "dataCot BETWEEN #" & Now() & "# AND #" & DateAdd("d", -dias, Now()) & "#")
112

Any thoughts about this
Thanks for your help

 

[Minty]

Do your DataCot fields have a time component or are they simply dates?

If so use Date() rather than Now() as a date of 4/10/2021 is actually stored as
4/10/2021 00:00:00

e.g. it has a time component, which will skew your where clauses expected results.

 

[cpampas]

Unfortunatly date() and now() produce the same error

 

[Gasman]

I get 23.3669997 in Excel after replacing , with . for my location?

 

[cpampas]

I noticed that the following returns the correct result:
Davg("[pLast]", "qryCurrent", "dataCot > #" & DateAdd("d", -10, Now())& "#")

so why would the following return Null ?
davg("[pLast]", "qryCurrent", "dataCot >= #" & DateAdd("d", -10, Now()) & "# AND dataCot <= #" & now() & "#")

 

[Gasman]

Put the criteria into a string and Debug.Print that.?
Could be the mm/dd/yyyy format throwing it off?

 

[CJ_London]

date format needs to be US format of mm/dd/yyyy or the sql standard of yyyy-mm-dd

so 04/10/2021 is being treat as 10th April - is that what you want?

but 19/09/2021 will be treated as 19th September because there are only 12 months in the year, not 19 or more

also, since your code is using the function now, then you could just use

"dataCot BETWEEN Now() AND DateAdd('d', -10, Now())"

or

"dataCot BETWEEN Now() AND DateAdd('d'," & -dias & ", Now())"

not the change to single quotes around 'd'

 

[Minty]

I noticed that the following returns the correct result:
Davg("[pLast]", "qryCurrent", "dataCot > #" & DateAdd("d", -10, Now())& "#")

so why would the following return Null ?
davg("[pLast]", "qryCurrent", "dataCot >= #" & DateAdd("d", -10, Now()) & "# AND dataCot <= #" & now() & "#")

I'll go back to my original suggestion, stop using Now() if there are no times involved.
It adds a layer of doubt and will give you wrong results if there are no times involved with your comparison dates.
e.g. (I'm in the UK so allow for that) In the immediate window look at the following;

? Now()
04/10/2021 13:52:46
? Format(Date(),"dd/mm/yyyy hh:nn")
04/10/2021 00:00

Therefore
>= Now()
Is not the same as
>= Date()

If you want proof - create a query

SELECT * FROM qryCurrent Where dataCot >= #" & DateAdd("d", -10, Now()) & "# AND dataCot <= #" & now() & "#"

And run that and see what you get - also as gasman suggest are you in the UK or US - as the date formatting will bite you.
Then try

SELECT * FROM qryCurrent Where dataCot >= #" & DateAdd("d", -10, Date()) & "# AND dataCot <= #" & Date() & "#"

I would almost bet money you get different numbers of records.

 

[cpampas]

Thank you all for the help solving this issue
I ended up using the syntax provided by CJ_London, that worked just fine. the only thing I hadnt tought about was that when subtracting days from the current date, I wanted to exclude saturdays and sundays, so I guess I will have to find a function to count the number o non working days and add those as well
Cheers

 

[gemma-the-husky]

It might be easier to let your users pick the dates they want.
Alternatively, go back a fortnight to exclude 2 full weeks, assuming you aren't bothered about bank holidays.

 

[Gasman]

Thank you all for the help solving this issue
I ended up using the syntax provided by CJ_London, that worked just fine. the only thing I hadnt tought about was that when subtracting days from the current date, I wanted to exclude saturdays and sundays, so I guess I will have to find a function to count the number o non working days and add those as well
Cheers

Write one yourself.
Pass it the two dates or just the one if the second is always Date.
Then sum for any day =1 or 7 and set that as the function's return value and add to your date duration.

 

[cpampas]

Thanks Gasman, it worked fine

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) = 8 Then n = n + 1
Next
tiraFS = n
End Function

 

[Gasman]

8 ?

 

[cpampas]

hehe, that was 1 not 8

 

[cpampas]

by the way now that I try to set the numbers as a variable, I get a syntax error :

dim dias as integer
dias =10
vMedia=DAvg("[pLast]", "qryCurrent", "dataCot BETWEEN Now() AND DateAdd('d'," & -dias & ", Now())"

without the integer variable it works :

vMedia=DAvg("[pLast]", "qryCurrent", "dataCot BETWEEN Now() AND DateAdd('d', - 10, Now())")

 

[Gasman]

Why are you still using Now()?
Try just the variable without concatenation?

 

[Minty]

Try

dim dias as integer
dias = -10
vMedia=DAvg("[pLast]", "qryCurrent", "dataCot BETWEEN Date() AND DateAdd('d'," & dias & ", Date())"

 

[cpampas]

Working fine
Thank you all

 

[Gasman]

Yet you are not using the non working days?

 

[Pat Hartman]

As the others have pointed out several times, Date() and Now() are not equivalent. If you have a record with todays date:

#10/04/2021#, it will NOT be between Now() which is #10/04/2021 01:47 PM# and #10/12/2021# Because today's date without any time component is LESS THAN the Now() value.