DCount function in access without specific condition

Question

I am trying to get an Access report to identify if a text field (NH) does not match with the same ID. For example, ID 179 has two rows but each NH is different (12345 & 12346). I am trying to use DCount to count the IDs where the NH does not match if it is the same ID but I cannot figure it out.

Here is an example of my code: This is supposed to get the ID's that match such as 179 and 179 and check the NH to see if the are the same and return a count if they are not.

CheckValue = DCount([ID], "vTestQuery", "[NH] <> '" & [NH] & "'" And "[ID] ='" & [ID] & "'")

This gives a value to Me.txtColor for a Conditional formatting I have set up if CheckValue has an actual value.

If (CheckValue > 0) Then
Me.txtColor = CheckValue

I also need this go through all the records in the report and get a count on each matching ID with different NHs so I can flag the NHs that are different.

Can someone let me know if I am even on the right track with this and if so a solution to my dilemma.

Thank you very much!

1st EDIT

Sample Data:

+-----+------------+------------+------------+-------------+
| ID  | FullName   | DateOfServ | AccountNum | NoteH       |
+-----+------------+------------+------------+-------------+
| 179 | Test, Jane | 8/1/2015   | 458585     | AAA-1111111 |
| 180 | Test, Paul | 8/1/2015   | 458586     | AAA-2222222 |
| 181 | Test, John | 8/2/2015   | 458587     | AAA-3333333 |
| 214 | Test, Alex | 8/3/2015   | 458588     | AAA-4444444 |
| 214 | Test, Alex | 8/3/2015   | 458588     | AAA-4444445 |
| 215 | Test, Alex | 8/3/2015   | 458589     | AAA-5555555 |
| 215 | Test, Alex | 8/3/2015   | 458589     | AAA-5555555 |
+-----+------------+------------+------------+-------------+

So what I need the report to do is highlight or change text color for the IDs that match but have a different NH For example record 214 has two records with all the same exact data except for the NoteH and I need both those NoteH to be highlighted or the text changed. I have made the NoteH in question both bold. Let me know if this helps.

2nd EDIT

So the query worked for all duplicate IDs with duplicate NoteH, but it still only registers one ID if the NoteH is different. I added an IDCount to show how the query registers each 214 ID as different because of the different NoteH.

Here are the results:

+-----+------------+---------+
| ID  | NoteCount  | IDCount |
+-----+------------+---------+
| 214 | 1          | 1       |
+-----+------------+---------+
| 214 | 1          | 1       |
+-----+------------+---------+
| 212 | 2          | 2       |
+-----+------------+---------+

I need a way to have the report recognize that 214 is a duplicate field but the NoteH is not the same. It is really close to working everything else you suggested works great!!!

3rd EDIT

SELECT May.ID, Count(May.ID) AS IDCount, FROM May INNER JOIN 
Count(CodeRyteCodingResults.[Note Handle]) AS NoteCount
CodeRyteCodingResults ON May.[Accession #] =
CodeRyteCodingResults.[Accession Number]
GROUP BY May.ID;

Sample data please. I have no context for what an NH is in terms of definition (fields, keys, values) or data (actual records). — Don Jewett
So the ID is an AutoNumber and the NH is a Short Text. I need to run through the records of the report and if the ID is the same such as 179 and 179 but the NH is different I need the NH field to change color. I can get the NH field to change color if I specify an ID but I need the report to check all ID's for multiple NH. Here is some sample data: ID: 179, Name: Test Allen, Date: 08/08/2015, NH: AAA-1234567... Then the next record would be like this: ID: 179, Name: Test Allen, Date: 08/08/2015, NH: AAA-1112223. I need the NH to flag on both of these because the ID is the same @DonJewett — Reuben Ellis
I'm hoping to see something like this edited into your question: stackoverflow.com/questions/14043162/… Without it, it doesn't really make sense to me — Don Jewett

Don Jewett Don Jewett · Accepted Answer · 2015-07-28T15:30:13

Use Aggregate Queries and Conditional Formatting

Create separate queries to get counts of unique notes per ID. It will look something like this (I'm using two queries):

UniqueNotesQuery

SELECT ID, NoteH
FROM BaseTable
GROUP BY ID, NoteH

NoteCountsQuery (this queries the first one)

SELECT ID, Count(NoteH) AS NoteCount
FROM UniqueNotesQuery
GROUP BY ID;

Then join this as part of your report query. It will look something like this:

SELECT BaseTable.*, NoteCountsQuery.NoteCount
FROM BaseTable INNER JOIN NoteCountsQuery
ON BaseTable.ID = NoteCountsQuery.ID

In your report, write a rule in the Conditional Formatting of the textboxes. Something like this:

[NoteCount] > 1

These queries above are just written from scratch, so they are untested, and you will need to flesh them out for your project.

References:

DCount function in access without specific condition

1 Answers