Conditional Formatting based on duplicates (1 Viewer)

OfficialChad

New member
Local time
Today, 11:35
Joined
Feb 13, 2020
Messages
23
Hello, again great access world! hope all is well and yall are safe!
I've come to you guys about Conditional Formatting issue i have come to This is the rule I'm using and it currently checks all the data from the table "Checks" which causes every amount in the report to go red because it's using all the data, but I just want it to check the data from today in the checks table how would I go about adding some sort of filter? I tried filtering the table but it still picks up all the data. If anyone has an easier way of doing this also I'm all ears!

Code:
DCount("*","Checks","[C_Amount]=" & [C_Amount])>1
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:35
Joined
Oct 29, 2018
Messages
21,358
Hi. Maybe something like?

Code:
DCount("*","Checks","[C_Amount]=" & [C_Amount] & " AND [DateField]=Date()")>1
 

OfficialChad

New member
Local time
Today, 11:35
Joined
Feb 13, 2020
Messages
23
Hi. Maybe something like?

Code:
DCount("*","Checks","[C_Amount]=" & [C_Amount] & " AND [DateField]=Date()")>1
This worked thank you! that was much simpler then i thought lol. anyway, i could also add a restriction like say ignore the duplicate (or not count it) if the person names are different from the report compared to the table???
 

OfficialChad

New member
Local time
Today, 11:35
Joined
Feb 13, 2020
Messages
23
Hi. Maybe something like?

Code:
DCount("*","Checks","[C_Amount]=" & [C_Amount] & " AND [DateField]=Date()")>1
example here see it counts all a duplicate even though the persons names are different
 

Attachments

  • Annotation 2020-04-10 162344.png
    Annotation 2020-04-10 162344.png
    9.5 KB · Views: 109

theDBguy

I’m here to help
Staff member
Local time
Today, 08:35
Joined
Oct 29, 2018
Messages
21,358
example here see it counts all a duplicate even though the persons names are different
Try adding each criterion one at a time.
Code:
DCount("*","Checks","[C_Amount]=" & [C_Amount] & " AND [DateField]=Date() AND [FirstName]='" & [FirstName] & "'")>1
 

OfficialChad

New member
Local time
Today, 11:35
Joined
Feb 13, 2020
Messages
23
Try adding each criterion one at a time.
Code:
DCount("*","Checks","[C_Amount]=" & [C_Amount] & " AND [DateField]=Date() AND [FirstName]='" & [FirstName] & "'")>1
the issues with it i think is that the First name and last name arent in the same table would i need to add another rule add the tabl into the function?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:35
Joined
Oct 29, 2018
Messages
21,358
the issues with it i think is that the First name and last name arent in the same table would i need to add another rule add the tabl into the function?
Yes, you could try that. You may have to use DLookup() too. Unless, the First Name and Last Name in this table is represented by an ID number, then you can just use it rather than the names.
 

OfficialChad

New member
Local time
Today, 11:35
Joined
Feb 13, 2020
Messages
23
Yes, you could try that. You may have to use DLookup() too. Unless, the First Name and Last Name in this table is represented by an ID number, then you can just use it rather than the names.
So i am using the ID represented ClientID (far right in picture)but now it seems it's not highlighting at all this is the code i have so far i think i put everything in the right place
Code:
DCount("*","Checks","[C_Amount]=" & [C_Amount] & " AND [Chased_Date]=Date() AND [ClientID]='" & [ClientID] & "'")>1
 

Attachments

  • Annotation 2020-04-10 180122.png
    Annotation 2020-04-10 180122.png
    11.2 KB · Views: 107

CJ_London

Super Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 19, 2013
Messages
16,553
ID's are usually numbers - with numbers you don't use the single quotes, suggest try

DCount("*","Checks","[C_Amount]=" & [C_Amount] & " AND [Chased_Date]=Date() AND [ClientID]=" & [ClientID])>1
 

OfficialChad

New member
Local time
Today, 11:35
Joined
Feb 13, 2020
Messages
23
no
ID's are usually numbers - with numbers you don't use the single quotes, suggest try

DCount("*","Checks","[C_Amount]=" & [C_Amount] & " AND [Chased_Date]=Date() AND [ClientID]=" & [ClientID])>1
oop sent the worng code yea i was using the non string version. but its still not doing it
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 19, 2013
Messages
16,553
what does 'still not doing it' mean? you get an error? wrong result? if so what?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 19, 2013
Messages
16,553
suggest to test that dcount is returning what you expect, temporarily put your dcount into your form recordsource and execute it
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 19, 2013
Messages
16,553
On the face of it, if your code is as you say and the clientID datatype is a number type then dcount should be returning a number other than 0.

If you are using it in a report then put it in your report recordsource - we are trying to confirm the dcount is returning a number other than 0 so the conditional formatting is triggered

And to be clear - you have set the text to red in the conditional formatting and there are no other conditions that might take precedence?
 

Users who are viewing this thread

Top Bottom