DCount Date field for duplicates giving Error# (2 Viewers)

[oxicottin]

Hello, I have a query (qry_EmployeesNet) with a field (ShiftDate) and I have an expression I want to use to count times the same date was used so I can us that expression in my continuous form to highlight duplicate dates. Anyways, I run the query, and the expression below displays (Error#) for the (DupDateCount) column. What am I doing incorrect in my expression, I think its the table part, I didnt know what to put there?

DupDateCount: DCount("ShiftDate","tblShiftDay","ShiftDate= """ & [ShiftDate] & """")

 

[sonic8]

You are not using the proper delimiters for a date: #

 

[oxicottin]

You are not using the proper delimiters for a date: #

If I use DupDateCount: DCount("ShiftDate","tblShiftDay","ShiftDate= #" & [ShiftDate] & "#") then my results give me a 2 for every record in this field?

 

[MajP]

Do an aggregate query (group by employee and shiftdate) maybe add where count > 1. Then if there is any record in this query for an employee and shift date you can do conditional formatting.

Then I would build a function

Public FunctionHasDupe(empID as long, ShiftDate as date) as boolean
  FunctionHasDupe = dcount("*","qryAggregate","employeeID = " & empID & " AND ShiftDate = #"  & format(shiftdate, 'MM/DD/YYYY') & "#") > 0
end if

You should be able to test hat function in the immediate window by passing an emp id and date.
You can add that function to your query if it is a continous form. If not you can call it in vba.

 

[Gasman]

Hello, I have a query (qry_EmployeesNet) with a field (ShiftDate) and I have an expression I want to use to count times the same date was used so I can us that expression in my continuous form to highlight duplicate dates. Anyways, I run the query, and the expression below displays (Error#) for the (DupDateCount) column. What am I doing incorrect in my expression, I think its the table part, I didnt know what to put there?

DupDateCount: DCount("ShiftDate","tblShiftDay","ShiftDate= """ & [ShiftDate] & """")

View attachment 120625

If you do not know what to put where, look up the syntax.