Hoping that someone can help sort this one out as I've spent the last 2 hrs. on a Saturday morning in the office trying to get it to work!!!
To start with I have a table which holds all the dates of bank holidays from 2010 through to 2015.
Table Name : HolidayDates
Field that holds the dates : BankHols
I have another table which contains records of issues that have been raised, the date they were raised and date solved.
TableName : LocalOutOfSLA
Field that holds date raised : EnteredDate
Field that holds date solved : SolvedDate
The first query I use is an update query and uses DCount to count the number of days that are bank holidays that fall between the EnteredDate and the SolvedDate and puts the value into a field within the table.
DCount("[HolDate]","HolidayDates","[HolDate] Between " & CDbl([EnteredDate]) & " AND " & CDbl([SolvedDate]) & "")
This query works just fine, the second query I have is pretty much the same but it counts the number of bank holidays that fall between the EnteredDate and the current date and should put the values into another field within the table, it is this query that does not want to work.
DCount("[HolDate]","HolidayDates","[HolDate] Between " & CDbl([EnteredDate]) & " AND " & CDbl(Now()) & "")
When I run it I just get back empty records and I've tried re-writing it a number of times but each time get the same issue, I have no idea why one would work and the other not....
Any thoughts would be greatly appreciated
Michelle
To start with I have a table which holds all the dates of bank holidays from 2010 through to 2015.
Table Name : HolidayDates
Field that holds the dates : BankHols
I have another table which contains records of issues that have been raised, the date they were raised and date solved.
TableName : LocalOutOfSLA
Field that holds date raised : EnteredDate
Field that holds date solved : SolvedDate
The first query I use is an update query and uses DCount to count the number of days that are bank holidays that fall between the EnteredDate and the SolvedDate and puts the value into a field within the table.
DCount("[HolDate]","HolidayDates","[HolDate] Between " & CDbl([EnteredDate]) & " AND " & CDbl([SolvedDate]) & "")
This query works just fine, the second query I have is pretty much the same but it counts the number of bank holidays that fall between the EnteredDate and the current date and should put the values into another field within the table, it is this query that does not want to work.
DCount("[HolDate]","HolidayDates","[HolDate] Between " & CDbl([EnteredDate]) & " AND " & CDbl(Now()) & "")
When I run it I just get back empty records and I've tried re-writing it a number of times but each time get the same issue, I have no idea why one would work and the other not....
Any thoughts would be greatly appreciated
Michelle