Create an "ID Code" from 2 fields (1 Alpha, 1 Date) 1

[LokiOfGameSlave]

Hi folks,

I have a "dateabase" where I have a 4 columns (as below):

ClientID Last Name First Name First Visit
SMI140902 Smith John 14/09/2002

Currently the ClientID field is manually populated by the user. What I would like is for the field to automatically populate itself based on the "Last Name" and "First Visit" fields. As you can see the "ClientID" field is the first 3 digits (capitalized) of the clients name and DDMMYY.

Is there any way of doing this without having to use Visual Basic? Or do I have to go post a request for the coding in the Visual Basic forum?

TIA,
-Loki

Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[Wray69]

Hey Loki,

Not sure if you are using EXCEL or ACCESS, if you are using EXCEL you could do something like this;
=LEFT(B1,3)&IF(DAY(D1)>9,DAY(D1),"0"&DAY(D1))&IF(MONTH(D1)>9,MONTH(D1),"0"&MONTH(D1))&RIGHT(YEAR(D1),2)

Assuming the last name is in B1 and the date in D1. If you are using ACCESS Im not sure how to do it.

Hope this Helps.

Cheers,

Wray

 

[LokiOfGameSlave]

Sorry about that. I don't post here too often so I tend to not think about what I should and should not put in the post.

I am using Excel 97.

And your Formula works beautifully

Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[LokiOfGameSlave]

One more thing. If the "First Visit" field is blank (either because the person entering the data doesn't know the "First Visit" date or is not yet at that field) is there any easy way to keep the "ClientID" field BLANK? I want to keep the "confusion level" for the user down as low as possible.

I tried adding &IF(ISBLANK(D1), " &quot to the end of your code, but 000100 still populates. :
Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[Dreamboat]

=if(isblank(D1),LEFT(B1,3),LEFT(B1,3)&IF(DAY(D1)>9,DAY(D1),"0"&DAY(D1))&IF(MONTH(D1)>9,MONTH(D1),"0"&MONTH(D1))&RIGHT(YEAR(D1),2)

Might be off by a parenthesis or two...

I just added this to the front of it:

=if(isblank(D1),LEFT(B1,3),

Always put the "smaller" part in front, not in back.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Wray69]

think Dream boat is missing a parenthesis off of the end but other than that looks good to me.
=IF(ISBLANK(D1)," ",LEFT(B1,3)&IF(DAY(D1)>9,DAY(D1),"0"&DAY(D1))&IF(MONTH(D1)>9,MONTH(D1),"0"&MONTH(D1))&RIGHT(YEAR(D1),2))

Cheers,

Wray

 

[LokiOfGameSlave]

Sweet. You guys (and gals) rock.

I amended to code slightly to force the Alpha characters to UpperCase. Here is the amended code:

=IF(ISBLANK(D1)," ",UPPER(LEFT(B1,3))&IF(DAY(D1)>9,DAY(D1),"0"&DAY(D1))&IF(MONTH(D1)>9,MONTH(D1),"0"&MONTH(D1))&RIGHT(YEAR(D1),2))

Thanks again for the help folks, this forum is the best I've seen yet.


Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[Dreamboat]

LOL
I read your post to say that you wanted the client ID part without the date, not that you wanted it to be blank.

YOU must have changed your post, right?
LOL

Of course...it was...um...my dyslexia?...no...I don't have that...um....old age?...maybe!

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[LokiOfGameSlave]

heh. I prefer the "too much blood in my caffeine stream" excuse.

NEVER use age as an excuse. Not good.

Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods