Solved DCount Date field for duplicates giving Error#

[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.

 

[arnelgp]

i was guessing to do the DCount() on table tblShiftsMachinesRan?
and add the EmployeeID on the Criteria:

DupDateCount: DCount("1","tblShiftsMachinesRan","ShiftDate= #" & Format(tblShiftsMachinesRan.[ShiftDate],"mm/dd/yyyy") & "# And EmployeeID = " & tblShiftsMachinesRan.EmployeeID)

 

[The_Doc_Man]

Seems to me that there is a default query wizard for finding duplicate entries. If your concern is over tblShiftDay then build a duplicate query and see what it says.

Because that table appears to be a reference table (that is, another table will link to it), I think we need to know a little bit more about the purpose of that table. It LOOKS like it is recording info about the shift, which appears to have a dedicated supervisor.

If you REALLY need to know that there are no duplicates, then a secondary index with a NODUPS constraint might seem in order. Because the ShiftDayID field will certainly be the prime key and will have NODUPS set, and given that layout, you can help yourself to avoid this problem by just preventing anyone/anything from creating the duplicate in the first place. But you have TWO fields that appear to be IDs and that is confusing.

 

[Pat Hartman]

Autonumbers are used when you don't have a candidate key. If you have a unique candidate key, you can make that the PK. If your candidate key is multi-field or not controlled by you such as an SSN, then sometimes (for convenience) we use an autonumber as the PK but define a unique index on the candidate key.

 

[Ken Sheridan]

You can return a result table of all duplicated dates per machine per employee with a simple aggregating query:

SELECT MachineID, EmployeeID, ShiftDate
FROM tblShiftMachinesRan INNER JOIN tblShiftDay
ON tblShiftMachinesRan.ShiftDayID = tblShiftDay.ShiftDayID
GROUP BY MachineID, EmployeeID, ShiftDate
HAVING COUNT(*) > 1;

You can join this query to other tables as necessary, or, if you need to return an updatable recordset, you can call the DLookup function on the query.