duplicate query within an If statement

A

Alan

Hello All

I have a data base which I have inherited and Im trying to sort ...
In my data base I have an employee number which is unique to the individual,
however over time this has been 'overlooked' and now Im using a data base I
would hope to have this as the Primary key ...
The problem is ... some records have been created with no employee number,
wrongly entered employee number, something totally spurious and finally
entered more than once.

I have done the provided Duplicate query and confirm 100+ duplicates. I
figured for analysis purposes that I would create another colum on my master
table which would provide a defined output depending on the error which would
subseqently become searchable, however I cant fathom how to use the duplicate
query within the If statement ... My code so far is

EmpNoStatus: IIf((IsNull(PERSONNEL![Employee No])),"NO
EMPNO",IIf((Len(PERSONNEL![Employee No])<6),"INVALID","OK"))

I think I need to replace the OK with another If loop for duplicates

Any help would be grateful

Regards
 
P

peregenem

Alan said:
EmpNoStatus: IIf((IsNull(PERSONNEL![Employee No])),"NO
EMPNO",IIf((Len(PERSONNEL![Employee No])<6),"INVALID","OK"))

I think I need to replace the OK with another If loop for duplicates
Click to expand...

'Loop' is the wrong mental model. Think in terms of sets. If you want
to include another set (e.g. your set of duplicates) in your resultset,
use UNION.
 
Top