DCOUNT not working

[jcarroll01]

Hi,

I'm trying to find a count of events in my table that occur in the last 90 days.

This is the formula I'm using, but the value being returned is the count of the entire set of occurrences.

Last90: DCount("[LastOfEXCEPTION_CODE]","TardySelect","[DATE] between Date() and Date()-90")

What might I be doing wrong?

 

[vbaInet]

Welcome to AWF!

Rename your [DATE] field. That's an Access reserved name.

 

[jcarroll01]

Thanks. I have seen that as a possible issue referenced elsewhere, but I changed it and it had no affect on the output.

Example:

Jimmy Johnson Last90 = 20 tardies and all dates = 20 tardies.

All of Jimmy's tardies are not, however, in the last 90 days.

 

[boblarson]

Your criteria is in the wrong order it should be:

[DATE] between Date()-90 and Date()

 

[vbaInet]

Just wanted you to get that sorted first.

Here:

Last90: DCount("[LastOfEXCEPTION_CODE]","TardySelect","[DATE] between #" & Date() & "# AND #" & Date()-90 & "#")

I don't think the hash characters are necessary since you're using built-in date functions but just in case. You can take them off after successful testing to be:

Last90: DCount("[LastOfEXCEPTION_CODE]","TardySelect","[DATE] BETWEEN " & Date() & " AND " & Date()-90)

Not sure about your logic though.

Edit: Oops.. just noticed Bob's post

 

[jcarroll01]

I considered that and had to changed it to what is shown. Both ways return the same value, so I guess the "between" works both ways.

Sigh.

Thanks so far though...

 

[boblarson]

I considered that and had to changed it to what is shown. Both ways return the same value, so I guess the "between" works both ways.

No, it doesn't so something else may be up. Can you post a copy of the database with any sensitive data changed to test data?

 

[jcarroll01]

Well...it's a sub-query. The initial query of the DB table isolates the incidents by date. Now I want to simply count the incidents and group them by different date ranges...

Last 90
Last 80
Last 70...and so on

I guess I could send the sub-query output in a file. Let me try that.

Jon

 

[jcarroll01]

Try this...One person who should have 90 day tardies and others.

 

[boblarson]

Try this out in a query and see what you think:

SELECT Test.Name, Count(Date()-[Date]) AS NumberOfTardies, Partition(Date()-[Date],0,9999,10) AS DayRange
FROM Test
WHERE (((Test.Date) Between Date()-90 And Date()) AND ((Test.Type2)="Tardy"))
GROUP BY Test.Name, Partition(Date()-[Date],0,9999,10);

 

[jcarroll01]

Well...that works. It doesn't exactly get me what I need, but close. I guess if it only returned th value for 80: 89 I would be better off.

 

[jcarroll01]

I modified it to remove the breakout of days...limited it to the last 10 days and I'll subtract that from another 90 day query and get what I need.

Very helpful.

Thanks!