Error in expression box

[Guest]

Hi !
I am creating a database for keeping the records of all patients admitted in
my hospital.
I want to keep various fields like
1. Indoor patient number which will ba a unique field and also the primary
key for this table.
2. First Name
3. Last Name
4. Consultant name
5. Now there will be various fields that will include res phone numbers,
office phone numbers,
mobile numbers etc.
There will be an OLE field which will carry the photo of the patient
My problem is that:
I want to generate an unique ID for each of my patients .
I want to generate the IP No like this :

First three letters of the first name of the patient/First three letters of
the last name of the patient/
A Random number to be generated by access

To generate this I set the default value of the field "IP No" in the form as =
Left(Trim [First Name],3)/Left(Trim[Last Name],3)/rnd in the expression box .

But when I go out from the design view of the form it says #Err

Where am I going wrong?

Thanks for your time

Dr Alok Modi MD

 

[John Vinson]

First three letters of the first name of the patient/First three letters of
the last name of the patient/
A Random number to be generated by access

This is called an "Intelligent Key" - unfortunately, that's not a
compliment.

It will end up being a pretty useless, meaningless string; it will be
VERY hard to keep it straight; it will not help your users locate a
patient (is Li Wu in there as LI_WU_1 or LIWU2 or what?), and it will
be hard to meet both the criteria that the number is random (which
implies that duplicates are allowed) and unique (which requires that
duplicates are forbidden).

I'd suggest just using an Autonumber and concealing it from user view.

John W. Vinson[MVP]

 

[Guest]

Thanks
but when I use the autonumber data type in the table it also generate
negative numbers.
Also i would have preferred if the IP no went somewhat like this:
ABC/DEF/0-8932782

Thanks
Dr Alok Modi MD

 

[John Vinson]

Thanks
but when I use the autonumber data type in the table it also generate
negative numbers.
Also i would have preferred if the IP no went somewhat like this:
ABC/DEF/0-8932782

Thanks
Dr Alok Modi MD

Let me reiterate:

I'd suggest just using an Autonumber and **concealing it from user
view.** It doesn't MATTER if the autonumbers are negative, if nobody
(other than a programmer debugging code) is ever going to see it!

You can generate this intelligent, complex key using VBA code.

It will require some programming to check for uniqueness (random
numbers, by their very definition, are *random* and you cannot be sure
that the same number won't come up a second time).

You will quickly find that this code is NOT mnemonic, and not
particularly useful. This kind of key was pretty common back 30 or 40
years ago, when you needed filing cabinet folders with quickly
readable codes, and when computers could only sort efficiently on one
field. With Access it is not NECESSARY to have such a code - you can
use the internal autonumber to link tables, and use Forms to search
for patients based on their name and other biographical data. Rather
than you or your staff having to remember that VIN/JOH/0-1923465 is
John Vinson, you can use a Combo Box and see "Vinson, John W." while
the computer "sees" -224912335; both the user and the computer go away
happy!

That said... you cannot use the Default property to generate your
intelligent key. The Default is applied the instant the record is
created; at that point, neither the first name nor the last name
exist anywhere in the database. You'll need to use some (rather
complex) VBA code to construct the code in the afterupdate events of
the first and last name textboxes, with a check to be sure it's
actually unique.

John W. Vinson[MVP]

 

[Guest]

Thanks John , I agree with what you say. The reason I don't want a negative
number as an IP number is that it will go as an entry on the indoor papers
and to the mediclaim and insurance companies. It won't look good if I send an
indoor number as a negative number and patients won't like it if I put a
negative number on the discharge summary. Then again if I were to adopt an
autonumber and type it as an increment one rather than a random number, then
if I have to delete a record for some reason (like my staff admits a patient
but patient decided to go home on outpatient basis, so the record has to be
deleted ) then access will take the next incremental number and so there is a
gap in the IP number which does not look good.
So any suggestions besides writing VBA as I am "null " in VBA. As it is I
am new to access and I am getting hooked on to it.

Thanks
Dr Alok Modi MD