Problem with dates in a DAvg query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Ortt - 16 Jul 2007 14:18 GMT

Could someone please tell me why the following section of code gives -317
days average dwell when there is only one part, (for info [Date Raised] is
24/11/2000 and [theMonth] is 01/11/200)?

DwellAvg: CInt(DAvg("datediff('d',Dateserial(year([Date Raised]),month([Date
Raised]),day([Date Raised])),
#" & DateSerial(Year([theMonth]),Month(DateAdd('m',1,[theMonth])),1) & "#)",

Could it be something to do with UK/US date formats and if so how can I
remedy this?

I know it is a problem in this part of the code as the query gives the
correct count of entries if I change the DAvg into a DCount.

Any help greatly appreciated.

Thanks,

John

and incase you think it is necesary the rest of the code follows:

"[tblQueryInfo]","
([Complete Date] is null or
(((month([Complete Date])>=" & Month([theMonth]) & " and
year([Complete Date])=" & Year([theMonth]) & ") or
year([Complete Date])>" & Year([theMonth]) & "))) and
((month([Date Raised])<=" & Month([theMonth]) & " and
year([Date Raised])=" & Year([theMonth]) & ") or
year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message

Douglas J. Steele - 16 Jul 2007 14:37 GMT

Using Year, Month and Day on a date field isn't going to help your date
format issues. [Date Raised] and DateSerial(Year([Date Raised]),Month([Date
Raised]),Day([Date Raised])) will always be the same (and will depend on
whether or not Access respects the date format correctly). And your #
delimiters aren't correct, and you're using DAvg incorrectly (DAvg is a
domain aggregate function that will compute the average for a specific field
in a table, using an optional Where clause).

What exactly are you trying to calculate? Are Date Raised and theMonth
parameters, fields in the table or controls on a form?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Could someone please tell me why the following section of code gives -317
> days average dwell when there is only one part, (for info [Date Raised] is
[quoted text clipped - 27 lines]
> year([Date Raised])=" & Year([theMonth]) & ") or
> year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message

John Ortt - 16 Jul 2007 16:10 GMT

I am glad someone knows what they are doing Doug! I am having a nightmare
here...

I am trying to find the average dwell time for the queries on a monthly
basis.

To do this I have first written a guery which groups the dates into months
[theMonth] and I am then trying to use this to find the average query dwell
for each month.

Essentially I want to look at all the queries which were started and not
completed at the start of the month in question then measure how long each
one had been outstanding in days and take the average.

The data is originally from a table called [tblQueryInfo].

If you can offer any pointers I would greatly appreciate it.

> Using Year, Month and Day on a date field isn't going to help your date
> format issues. [Date Raised] and DateSerial(Year([Date
[quoted text clipped - 38 lines]
>> year([Date Raised])=" & Year([theMonth]) & ") or
>> year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message

Douglas J. Steele - 16 Jul 2007 16:34 GMT

So Date Raised is a field in tblQueryInfo?

How is theMonth defined in the query?

If you've got a query raised on 29 June, and ending 02 Aug, I assume you
want that treated as 2 days in June, 31 days in July and 2 days in August?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am glad someone knows what they are doing Doug! I am having a nightmare
>here...
[quoted text clipped - 56 lines]
>>> year([Date Raised])=" & Year([theMonth]) & ") or
>>> year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message

John Ortt - 17 Jul 2007 09:00 GMT

I use two queries to find all the Raised and Closed dates as follows:

SELECT DateAdd("d",1-Day([Complete Date]),[Complete Date]) AS theMonth,
Count(tblQueryInfo.[Query Number]) AS QueriesClosed
FROM tblQueryInfo
WHERE (((tblQueryInfo.[Complete Date]) Is Not Null))
GROUP BY DateAdd("d",1-Day([Complete Date]),[Complete Date])
ORDER BY DateAdd("d",1-Day([Complete Date]),[Complete Date]);

and the other query:

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS theMonth,
Count(tblQueryInfo.[Query Number]) AS QueriesRaised
FROM tblQueryInfo
GROUP BY DateAdd("d",1-Day([Date Raised]),[Date Raised])
ORDER BY DateAdd("d",1-Day([Date Raised]),[Date Raised]);

To ensure that I get all months even if a query was raised but not closed
(and vica versa) I then perform the following union query:

SELECT qryStatsTotalClosed.theMonth
FROM qryStatsTotalClosed

UNION SELECT qryStatsTotalRaised.theMonth
FROM qryStatsTotalRaised
ORDER BY theMonth;

As to your question below you are correct Doug. I would expect it to credit
2 days for June, 31 for July and 2 for August.
The resulting average dwells would use 2 days for the June figure, 33 for
July and 35 for August.

Hope that clarifys things somewhat and thanks again,

John

> So Date Raised is a field in tblQueryInfo?
>
[quoted text clipped - 64 lines]
>>>> year([Date Raised])=" & Year([theMonth]) & ") or
>>>> year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message

Douglas J. Steele - 17 Jul 2007 11:57 GMT

Try:

DwellAvg: CInt(Avg(DateDiff('d',[Date Raised], DateSerial(Year([theMonth]),
Month([theMonth]) + 1,1))))

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I use two queries to find all the Raised and Closed dates as follows:
>
[quoted text clipped - 101 lines]
>>>>> year([Date Raised])=" & Year([theMonth]) & ") or
>>>>> year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message

John Ortt - 17 Jul 2007 15:03 GMT

I tried that Doug but it didn't seem to work as [theMonth] value was
calculated.

Having said that I think I have found a way forward:

I was having so little success that I decided to simplify the task somewhat.

I created a new database with a single table [tblData] and two fields
[startDate] and [endDate].

I then added four rows of data and tried to repeat the queries knowing what
to expect. (Table data shown below)

startDate endDate
01-jan-06 02-Feb-06
01-Feb-06 01-Dec-06
01-Jan-06 01-Jan-07
01-Jun-06 01-Dec-06

I first created a Union Query [qryMonths] to define the months (as shown
below):

SELECT DateAdd('d',1-Day([startDate]),[startDate]) AS dataByMonth
FROM tblData
GROUP BY DateAdd('d',1-Day([startDate]),[startDate])

UNION SELECT DateAdd('d',1-Day([endDate]),[endDate]) AS dataByMonth
FROM tblData
GROUP BY DateAdd('d',1-Day([endDate]),[endDate]);

I now tried to calculate the number of outstanding queries at each time
point in the database, knowing that the result should look as follows:

monthlyStats Outstanding
01/02/2006 3
01/03/2006 2
01/07/2006 3
01/01/2007 1
01/02/2007 0

My first attempts were unsuccessful but it was quite apparent that this was
because the date didn't seem to be registering correctly.

This led me to try adding the date using the dateserial format as shown in
the query below and it worked.

SELECT DateAdd('m',1,[dataByMonth]) AS monthlyStats,
DCount("startDate","tblData","endDate>=Dateserial(" & Year([dataByMonth]) &
"," & Month([dataByMonth])+1 & ",1) and
startDate<=Dateserial(" & Year([dataByMonth]) & "," &
Month([dataByMonth])+1 & ",1)") AS Outstanding
FROM qryMonths
ORDER BY DateAdd('m',1,[dataByMonth]);

Now to try to do this in the main database. I'll let you know how I get on.

Equally if you can think of a more graceful solution I would love to know.

Thanks again for all your help, it is greatly appreciated.

All the best,

John

> Try:
>
[quoted text clipped - 106 lines]
>>>>>> year([Date Raised])=" & Year([theMonth]) & ") or
>>>>>> year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message

Douglas J. Steele - 17 Jul 2007 15:47 GMT

The following SQL gives me

monthlyStats Outstanding
01/02/2006 3
01/03/2006 2
01/07/2006 3
01/01/2007 1

SELECT DateAdd('m', 1, qryMonths.dataByMonth) AS monthStats, Count(*) As
Outstanding
FROM tblData INNER JOIN qryMonths
ON tblData.startDate <= DateAdd('m', 1, qryMonths.dataByMonth)
AND tblData.endDate >= DateAdd('m', 1, qryMonths.dataByMonth)
GROUP BY DateAdd('m', 1, qryMonths.dataByMonth)
ORDER BY DateAdd('m', 1, qryMonths.dataByMonth)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I tried that Doug but it didn't seem to work as [theMonth] value was
>calculated.
[quoted text clipped - 172 lines]
>>>>>>> year([Date Raised])=" & Year([theMonth]) & ") or
>>>>>>> year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message

John Ortt - 17 Jul 2007 16:35 GMT

As I expected Doug, I was sure you could develop a more elegant solution.

Thankyou

> The following SQL gives me
>
[quoted text clipped - 190 lines]
>>>>>>>> year([Date Raised])=" & Year([theMonth]) & ") or
>>>>>>>> year([Date Raised])<" & Year([theMonth]) & ")"))

Reply to this Message