Access (Expression) Calculated Field Code

Howie2018

New member
Local time
Today, 14:56
Joined
Jun 4, 2018
Messages
2
Hi All

I am creating a database and I want to prevent duplicate records on data entry. I want it based on Firstname, Surname, E-mail address and Department.

I have put the following code in the calculated field in an expression but it doesn't like it.

=IIf(DCount("*","[ALL STAFF]","[FORENAME]<>" & Nz([FORENAME],0) & "[SURNAME]<>" & Nz([SURNAME],0) & " And [DEPARTMENT]<>" & Nz & "[DEPARTMENT],0) &" And [E-MAIL] = '" & Replace(Nz([E-MAIL]),"'","''") & "'")>0,"RECORD ALREADY EXIST","")

Can you help please?
 
Welcome to the forum. I seem to remember your user name from elsewhere(?)

There are lots of errors in your code
Recommend that you instead use a primary key field for this purpose e.g. PersonID and just check against that.
 
Further to Colin's advice, you could consider a surrogate(autonumber) PK and a unique composite index composed of Firstname, Surname, E-mail address and Department.

Good luck.
 

Users who are viewing this thread

Back
Top Bottom