Solved conditional formatting duplicates

[quest]

Hi,
I have put conditional formatting to almost every field. of course if there is no logic no need to put one. last one that i don't know how to make is this. i want my field to check previous field and if value is same to be red. every value is unique no duplicates and this count only for one year. if the duplicate is form another year it does not count. so the same year only.

 

[tvanstiphout]

I would add a field to the form's underlying query, which would show a True or False value depending on your criteria. Then the CF expression is super simple.

 

[quest]

I would add a field to the form's underlying query, which would show a True or False value depending on your criteria. Then the CF expression is super simple.

can you elaborate that little more i am not at that level it is to complex for me. for now i understand that i should add field in the query on which form is based.

 

[DHookom]

Elaborating would be easier if you provided a table structure and possibly significant field descriptions.

 

[theDBguy]

To determine the value from a “previous” record, you may need to use a subquery. Maybe try to do a search on that.

 

[quest]

Elaborating would be easier if you provided a table structure and possibly significant field descriptions.

here is screenshot of this little database with all conditional formatting. help i need is for the last field (hyperlinks) where this field for last two records has same value. if conditional formatting works value in the third record in hyperlink field should be red.

 

[tvanstiphout]

can you elaborate that little more i am not at that level it is to complex for me. for now i understand that i should add field in the query on which form is based.

This requires a fairly complex query. You may want to post the essential parts of your db so we can do it for you.
If you want to first delve into it yourself, here is a starting point: http://allenbrowne.com/subquery-01.html. And another one: https://learn.microsoft.com/en-us/a...ess-query-referencing-value-from-previous-row

 

[DHookom]

If the number of records isn’t large, you could add a new column to the query using DCount().

FindDup: DCount("*", "[your table]", " PKField<>" & PKField & " And Year(SomeField) = " & Year(SomeField))

I typed this on my iPad so there are bound to be errors.

 

[MajP]

Where this field for last two records has same value.

This is confusing. When we say duplicates we mean having the same value repeated in the same field. But your example could be that or you could mean having the same value in two different fields. You show both.

If you mean two different fields then you can create a new calculated column
iif([field1] = [Field2],True,False)
Then do conditional formatting on the calculated column where True is red

I interpret this

Where this field for last two records has same value.

to possibly mean where the "value in the last two fields are the same".

 

[quest]

This requires a fairly complex query. You may want to post the essential parts of your db so we can do it for you.
If you want to first delve into it yourself, here is a starting point: http://allenbrowne.com/subquery-01.html. And another one: https://learn.microsoft.com/en-us/a...ess-query-referencing-value-from-previous-row

This requires a fairly complex query. You may want to post the essential parts of your db so we can do it for you.
If you want to first delve into it yourself, here is a starting point: http://allenbrowne.com/subquery-01.html. And another one: https://learn.microsoft.com/en-us/a...ess-query-referencing-value-from-previous-row

i will read these later but now i need help. here is the database.

This is confusing. When we say duplicates we mean having the same value repeated in the same field. But your example could be that or you could mean having the same value in two different fields. You show both.
View attachment 122144

If you mean two different fields then you can create a new calculated column
iif([field1] = [Field2],True,False)
Then do conditional formatting on the calculated column where True is red

same value, same field two different records. it is about field with hyperlinks (Dokument or Документ) and not last two fields Where this field for two records has same value.

 

[CJ_London]

You might be able to do this with a vba function. Not at my computer at the moment but will take a look later

 

[MajP]

same value, same field two different records. it is about field with hyperlinks (Dokument or Документ) and not last two fields Where this field for two records has same value

OK. Then I would look at some version of what @DHookom suggests. You can then format where count is > 1.

 

[quest]

OK. Then I would look at some version of what @DHookom suggests. You can then format where count is > 1.

i tried something like DCount("*";"tbl_Boleduvanja";"[YearSearching]=" & [YearSearching] & " AND [Dokument]='" & [Dokument] & "'")>1 this didn't work this one too DCount("*";"tbl_Boleduvanja";"[YearSearching]=" & [YearSearching] & " AND [Dokument]='" & [Dokument] & "' AND [BoleduvanjaID]<" & [BoleduvanjaID])>0 the same

 

[MajP]

See if this example helps. One uses the Dcount. The other query uses an aggregate query to get the counts.

 

[MajP]

If you notice the one using the Dcount is editable. The second uses an aggregate query which makes it non updateable. If you need to update the form then the dcount is the easiest.

 

[quest]

If you notice the one using the Dcount is editable. The second uses an aggregate query which makes it non updateable. If you need to update the form then the dcount is the easiesin my form

If you notice the one using the Dcount is editable. The second uses an aggregate query which makes it non updateable. If you need to update the form then the dcount is the easiest.

my form is updateable i mean nothing is changed when i use red it means something need to be fixed so when i change that value and new is not duplicate red disappear

 

[MajP]

my form is updateable i mean nothing is changed when i use red it means something need to be fixed so when i change that value and new is not duplicate red disappear

My point is that there are many queries that you can use to return the duplicates. Most of these require a subquery or aggregate query which makes the whole query not updateable and thus make your form not updateable. Therefore, if you need your form updateable, use the first technique with a dcount function.

 

[CJ_London]

is this what you mean?

if so see the attached - a short function which is then called from your query

 

[quest]

is this what you mean?
View attachment 122146

if so see the attached - a short function which is then called from your query

yes that was what i wanted. bingo. thank you.

 

[KitaYama]

is this what you mean?


if so see the attached - a short function which is then called from your query

It doesn't work when you have two (or more) sets of duplicates: (If I understand the request correctly)

Also, If it's triplicate (instead of duplicate), only the second one is red. The third one remains normal)
Whether it's what OP requires or not, I don't know.