Display only part of text box's value? 1

[straybullet]

We have a client DB. In the Customer table, there are LastName, FirstNameM, FirstNameF, etc.
In order to keep the 5 John Smith's from showing as duplicate records, we've been adding a digit onto the FirstNameM. Unfortunately, we cannot simply use the address or phone fields to verify whether the records are true duplicates.
This has been working well. However, the big boss has decided he does not want to see the #'s on his reports.

Is there a way to set up a text box which would show the field value as is if there is no number in it or if there is, only display the text characters (without the # character)?

Let them hate - so long as they fear... Lucius Accius

 

[traingamer]

Typed directly here - not tested:
iif(isnum(right([FirstNameM], 1)), left([FirstNameM], len([FirstNameM]) -1), [FirstNameM])

If there is more than one number at the end, you'll have to adjust.

Why don't you just add an autonumber primary key since the lastname, firstname is obviously not unique?

traingamer

 

[straybullet]

TYVM for your answer - Im going to try it out tonite.

We do have a primary key for this table, however, with clients calling multiple times (often not reaching the same operator each time), time restraints, and of course there is always laziness to contend with - there is a high probability of them getting entered as a new contact.



Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

TY again for trying to help. Unfortunately, Access doesnt recognize isnum and is asking for a value for it. Any other suggestions?

Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

Well... Access doesnt want to recognize Type either...

=IIf(Type(Right([FirstName],1)=1),Left([FirstName],Len([FirstName])-1),[FirstName])

is asking for a value for type now. blech

Let them hate - so long as they fear... Lucius Accius

 

[traingamer]

Sorry - IsNumeric - not IsNum.

traingamer

 

[straybullet]

You're the bestest!! huggggggggggggggggggggggggggggs!!

Let them hate - so long as they fear... Lucius Accius