Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Derive member ID automatically from Last , First name and DOB? 1

Status
Not open for further replies.
Np02

Np02

Technical User
Mar 14, 2003
21
US
A little question, but I got stuck!

I am doing a form for entering members information. Field 1 is Last name, Field 2 is First Name, Field 3 is DOB (Date of Birth). The 4th field will be Member ID. I want the program produces the member ID automaticaly based on the above three fields' data. The member ID will come from the following combination: 'The first 3 letters of Last Name + The first 3 letter of First Name + The last 2 digits of Year of birth' . Ex: Merber John Doe, DOB 1-3-65. His Member ID will be "DOEJOH65". Just a trick, In case member's name is less than 3 letters, then program will insert a "*" to replace the no word. Ex: John Le, DoB 3-28-1945. ID will be "LE*JOH45". Same rule for whose first name less than 3 letters.

Anybody can help out?
 
Sort by date Sort by votes
Check out the LEN$(), LEFT$() , RIGHT$() and DATEPART() functions. When used correctly, they will return the information you want.

Hint: Right(DatePart("YYYY", #03/14/2003#) , 2 ) returns "03"

Len("YE") = 2

Ok?

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:
More Access stuff at
 
Upvote 0 Downvote
but the use of the asterisk in an ID is, perhaps, not the best choice. It serves as a 'wild card' character and could become somewhat of a problem.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
Upvote 0 Downvote
In the BeforeUpdate event of the form, try something like this:
Code: 
strID = Left$(strLastName, 3) & String(3 - Len(Left$(strLastName, 3)), "*") & Left$(strFirstName, 3) & String(3 - Len(Left$(strFirstName, 3)), "*") & Format(dteDOB, "yy")
 
Upvote 0 Downvote
Fancy:

Your code looks good to me, but I tried and it didn't work.

I have the frm_Member with 4 fields: LastName, FirstName, DOB, ID. I copied your code to Form Before-Update event procedure. Tried acouple of entry, didn't work. The ID field remians empty. Any problem of the code, please advise.

NP
 
Upvote 0 Downvote
As noted earlier, I DO NOT recommend using the asterisk as placeholder

Code: 
? basCreId("Red", "Michael", #8/21/42#)
RedMic42

Of course, to use this in a query you would pass the FIELDS, not the literals, and asign the return to the alais of your calculated field.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
Upvote 0 Downvote
Hi, Michael:

I do not have problem to replace asterisk with something else. What else letter or symbol will you suggest?

And, I still do not know how to change Fancy's code to make it work?

NP
 
Upvote 0 Downvote
I'll not comment on the other code.

In the small procedure I posted, look for the Const Pad = "$$$". the "$" is the replacement character. You can, of course, change it to any character you choose.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
Upvote 0 Downvote
... and WHAT is so important that YOU are already at work at ~~ 8 AM on a Saturday morning (ye olde U.S. East coast snobbery showing through)?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
Upvote 0 Downvote
Thank you, Michae. I will take your "$" then.:

I usually get up early, read the bible and pray. After I finish my prayer today, it just pop up in my mind that still something not resolved in Tek-Tips. That's all. Not serious, but just an attitudet to pursue the efficiecy and truth. How about you,what do U do this early? T get some early bird prize? By the way, how can U know I am in east cost?

Continue to pursue the truth: I do not ask you to comment on some other's code, but it is still not working. It is good to make it working.I hope Fancy, you or anybody can make this code working.

NP

 
Upvote 0 Downvote
I didn't guess that YOU were anywhere. U.S. East Coast is MY location. I no longer am able to sleep for long periods, so getting up is just a way to do something besides laying awake in a bed doing nothing. Tek-Tips is (usually) just a diversion to keep me occupied between searches for employment. The "jobs" boards appear to not know anything about time frames ...

MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
Upvote 0 Downvote
Now, I dont consider myself an expert but cant you just...

if Len(Last Name) >= 3 then

IDLastName = Left(Last Name, 3)

elseif Len(Last Name) = 2 then

IDLastName = Last Name & "$"

elseif Len(Last Name) = 1 then

IDLastName = Last Name & "$$"

endif


if Len(First Name) >= 3 then

IDFirstName = Left(First Name, 3)

elseif Len(First Name) = 2 then

IDFirstName = First Name & "$"

elseif Len(First Name) = 1 then

IDFirstName = Last Name & "$$"

endif

IDDOB = Right(DOB, 2)


MemberID = IDLastName & IDFirstName & IDDOB

You may have to do something to the DOB to convert it to a string first but I think this should work. Also you'll need to do some tests to ensure the user does enter something for the first name, last name and DOB. Also declare IDLastName, IDFirstName and DOB as Strings.
 
Upvote 0 Downvote
Question
What happens when you get two membersID the same
 
Upvote 0 Downvote
I don't know why my code didn't work for you. I just copied and pasted the code, from my previous post, into a new form and it worked fine. The code assumes the following:

1. strLastName is the name of the control that contains the Last Name
2. strFirstName is the name of the control that contains the First Name
3. dteDOB is the name of the control that contains the persons birthday (entered as a valid date).

You need to place the code in the BeforeUpdate event of the form. Or place the code in its own procedure and call it in the AfterUpdate event of each of the 3 fields. Note that you should also call it via the OnCurrent event of the form.

I agree with MichaelRed, I would use something other than an asterisk.

Placing the following code in the ControlSource property of the ID field will also work.
=IIf((Not IsNull([strLastName])) And (Not IsNull([strFirstName])) And (Not IsNull([dteDOB])) And IsDate([dteDOB]),Left$([strLastName],3) & String(3-Len(Left$([strLastName],3)),"*") & Left$([strFirstName],3) & String(3-Len(Left$([strFirstName],3)),"*") & Format([dteDOB],"yy"),"")

If you don't want to do it via the ControlSource property, than try this code in the BeforeUpdate event of the form. (It's pretty much the same as my first post, but makes a few checks.)

strID = IIf((Not IsNull([strLastName])) And (Not IsNull([strFirstName])) And (Not IsNull([dteDOB])) And IsDate([dteDOB]),Left$([strLastName],3) & String(3-Len(Left$([strLastName],3)),"*") & Left$([strFirstName],3) & String(3-Len(Left$([strFirstName],3)),"*") & Format([dteDOB],"yy"),"")
 
Upvote 0 Downvote
Hi; FancyPraire:

Great, it works. Good code. Get a star from me.

NP
 
Upvote 0 Downvote
If I were you I would highly consider not using the "*" (or any other special character) as a default in your fields. This is the wild character in Access and running queries, modules and reports at a later date is going to give you some headaches.



"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"
 
Upvote 0 Downvote
I have not tested the pieces of code above, so I'll assume they work.

However, I have a question:

Is it impossible that two members have the same name and same date of birth?

John Smith, DOB 1-3-65, New York
John Smith, DOB 1-3-65, Philadelphia

Wow, you'll have headaches...Either give up this approach while you still can, or buy a lot of aspyrine.

And (especially for WildHare):
This is exacly what I meant in thread thread700-503943
Any combination of meaningful data can be duplicated in a database, so the ID will be unusable.

Sorry for intruding...

[pipe]
Daniel Vlas
Systems Consultant

 
Upvote 0 Downvote
And (especially for WildHare):
This is exacly what I meant in thread Thread700-503943
Any combination of meaningful data can be duplicated in a database, so the ID will be unusable.

I agree - which is why "membership" databases tend to be such a pain in the buttocks - it's usually a major effort to come up with SOME kind of unique identifier - you can tell that MOST organizations don't bother, because you get 17 freaking pieces of mail from the same dam' group..

I still, however, stand by my original contention that using an autonumber field as the sole primary key in a table is useless.

See ya around! [bigsmile]

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:
More Access stuff at
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

PrimoSteve
  • Locked
  • Question
Grid From 1
2
Replies
21
Views
1K
PrimoSteve
PrimoSteve
PrimoSteve
  • Locked
  • Question
Fill in 2nd Textbox2 from Textbox1
Replies
3
Views
389
PrimoSteve
PrimoSteve
caykamanj1966
  • Locked
  • Question
Enter information automatically
Replies
5
Views
175
Andrzejek
Andrzejek
Bubba002
  • Locked
  • Question
Pass a subform fieldname-ID as a string variable in CF expression
2 3
Replies
50
Views
1K
dhookom
dhookom
TomSalvato
  • Locked
  • Question
Getting the Monday date from a week number 1
Replies
2
Views
472
TomSalvato
TomSalvato

Part and Inventory Search

Sponsor

Back
Top