Problem with DCount and evaluating Dates (1 Viewer)

[chugo]

Hello,

I'm having an issue with the DCount function. I want to get a count of action items that are beyond are past their due date Here's my code:



Dim rstActionItems As DAO.Recordset
Dim rstRoadmapStatus As DAO.Recordset

Dim SelectActionItems As String

Dim HotelStatus As Integer
Dim CurrentInnCode As String
Dim TotalIncomplete As Integer
Dim TotalAtRisk As Integer
Dim TotalMovingBack As Integer
Dim TotalOnTarget As Integer

Dim TodaysDate As Date

TodaysDate = Date

TotalIncomplete = DCount("ActionItemID", "tblHotel_ActionItems", "InnCode = '" & InnCode & "' AND Complete = False")
'TotalAtRisk = DCount("ActionItemID", "tblHotel_ActionItems", "InnCode = '" & InnCode & "' AND Complete = False AND ActionItemDueDate < " & TodaysDate)
TotalAtRisk = DCount("InnCode", "tblHotel_ActionItems", "InnCode = '" & InnCode & "' AND ActionItemDueDate < " & TodaysDate)

Debug.Print (TotalIncomplete)
Debug.Print (TotalAtRisk)

'Added This to See if there was something wrong with the date field
SelectActionItems = "SELECT ActionItemID,ActionItemDueDate,Complete FROM tblHotel_ActionItems WHERE InnCode = '" & InnCode & "' AND Complete = False"

Set rstActionItems = CurrentDb.OpenRecordset(SelectActionItems)

If Not rstActionItems.RecordCount = 0 Then


rstActionItems.MoveFirst

'Loop through each record to determine if the Action Item is complete
Do Until rstActionItems.EOF

If rstActionItems.Fields("ActionItemDueDate") < TodaysDate Then


Debug.Print (rstActionItems.Fields("ActionItemDueDate"))


End If

rstActionItems.MoveNext


Loop

End If


It will not properly evaluate the date comparison in dcount. It thinks that all the dates are greater than today. I added a loop at the bottom to evaulate the same dates differently and it works fine. Is there some oddity with Dcount and evaluating dates is there something wrong with my code.

thanks!

 

[pbaldy]

See if this helps:

http://www.mvps.org/access/general/gen0018.htm

Is TodaysDate defined somewhere else?

 

[chugo]

Thanks. Didn't see anything in the link that helped me.

TodaysDate is not used anywhere else that I know of. It evaluates fine in the bottom part, just not in DCount. DCount sees every date as being greater than TodaysDate.

I could create the count by looping the records but it seems like Dcount is more efficient.

 

[pbaldy]

Really? The part about surrounding dates with # didn't seem appropriate?

 

[billmeye]

If you want to use dates in an aggregate function, you need to surround the date with ##:

TotalAtRisk = DCount("InnCode", "tblHotel_ActionItems", "InnCode = '" & InnCode & "' AND ActionItemDueDate < #" & TodaysDate & "#")

 

[chugo]

AH. Looked right past that. That worked! Here's the way I had to code it to not get a syntaxt error:

TotalAtRisk = DCount("InnCode", "tblHotel_ActionItems", "InnCode = '" & InnCode & "' AND ActionItemDueDate < " & " #" & Date & "#")

Thanks!

 

[pbaldy]

No problem!