Unique Reference Numbers?

[Rusty]

Hey guys,

Hope you can help with this one. I have a form called "frmPatientID", based on a query "qryPatientID" and table "tblPatientID".

I have a field called HospitalNo that is set to "text" and contains the patient's unique hosptal number of 123456, 008777, etc.

However, although the above numbers have to be unique, if the patient is out of the area they get a number of 000000 and this is not unique (i.e. more than one patient can be out of the hospitals area).

So how do I get the database to control this via a rule? (As in everything NOT 000000 needs to be unique). Is it on the form on an AfterUpddate or can I do it in the table?

Many thanks,

Rusty

 

[WayneRyan]

Rusty,

You can use the DCount function in the form's BeforeInsert AND BeforeUpdate
event to check it.

Wayne

 

[RV]

Use DCount in a Before Update event on your control box to check whether a hospital number has already been issued, excluding 000000.

RV

 

[Pat Hartman]

Rather than using a string of zeros as the default value, use null. That way you can define a unique index on the column and it will ignore nulls.

 

[Rusty]

I can't do that Pat - patients that are outside the Trust's administrative area are given '000000' as their reference number. So I'm stuck with this rather unique 'challenge'.

Rusty

 

[Pat Hartman]

You can ignore the zeros on input and display them on output:

Format(Nz(YourField,0),"000000")