Dlookup to Duplicates Query? 1

[misscrf]

I am trying to create a query for the control source of a continuous form. One of the purposes of this form is to use conditional formatting, so I can highlight "required" fields that are blank or don't validate, and also to highlight duplicate rows.

I have a method to accomplish this, but in the query I need to create a formula to look up the ID's that are identified in my duplicates query.

Here is the layout. I have a 3 queries

query1 - has duplicates, identified by 4 common fields.

query2 (qryCocWorkDupeIDs) - gives me the id's of the records which have those 4 common fields.

query3 - the record source for this form, which will be a preview of the data we are dealing with. It is the table with the data, and then I need 1 extra field, as an expression to identify if that row is an ID in query2.

This is what I have

iif(DLookUp("[ID]","qryCocWorkDupeIDs","[ID] in([ID])") = ID, "DUP", "")

This works for the min id in the query2, but not for any of the others. Is what I am doing wrong? Is there a better way to accomplish this?

Thanks!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I think your DLookup() is wonky. I would try:

IIf(DCount("[ID]","qryCocWorkDupeIDs","[ID]=" & ID)>0, "DUP", "")

I think a query with a subquery would be more efficient but this would depend on if you need your data to allow edits.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

wonky it was. You are a superstar!

Thank you, Duane

misscrf

It is never too late to become what you could have been ~ George Eliot