Miscellaneous Characters

[DRananahan]

I need some help!!!

I have a Customer Information form that allows input for either a company or FirstName MI and LastName. Another field on my form is for the Short Name. The short name is for the first 15 characters of either the company name or the individual's name.

My code pulls is as follows:

Private Sub ShortName_Enter()

Me.ShortName = IIf(IsNull([CompanyName]), Left(([Lastname] & " " & [FirstName] & " " & [MI]), 15), Left(([CompanyName]), 15))

My problem is this: In the CompanyName field, the user is to enter an "*" for how the name should be alphabetized (i.e. The*ABC Corporation) I need Access to check for the "*" and start the short name from there. Also, if the name (individual or company) has a "-" I need it removed and a space inserted.

Any help would be much appreciated.

Thank you,
Doug

 

[D-Fresh]

Doug,

Run through a loop before you set the short name and find the asterisk...

dim TheLetter as string
dim ctr as integer
dim strBuildCompany as string
dim strBuildFirst as string
dim strBuildLast as string
dim StartPoint as integer

strBuildCompany = ""
strBuildFirst = ""
strBuildLast = ""

for ctr = 1 to len([CompanyName])
TheLetter = mid([CompanyName],ctr,1)
if TheLetter = "*" then StartPoint= ctr + 1

if TheLetter = "-" then
strBuildCompany = strBuildCompany & " "
else
strBuildCompany = strBuildCompany & mid([CompanyName],ctr,1)

end if
next

for ctr = 1 to len([FirstName])
TheLetter = mid([FirstName],ctr,1)

if TheLetter = "-" then
strBuildFirst = strBuildFirst & " "
else
strBuildFirst = strBuildFirst & mid([FirstName],ctr,1)

end if
next

for ctr = 1 to len([LastName])
TheLetter = mid([LastName],ctr,1)
if TheLetter = "*" then StartPoint=ctr

if TheLetter = "-" then
strBuildLast = strBuildLast & " "
else
strBuildLast = strBuildLast & mid([LastName],ctr,1)

end if
next

Then you can just use the mid function instead of the left function...

mid(([Lastname] & " " & [FirstName] & " " & [MI]), StartPoint,15)

This will start the name after the asterisk and will also get rid of the dashes... Hope that helps..

Doug

 

[DRananahan]

Doug,

Thank you for your help on this. I have one more question though...

I need to check the CompanyName field after update to verify that there is an "*" in the field. If there is not, I need to prompt the user to add it.

I am having a total brain freeze on how to do this.

If you could give me some direction it would be much appreciated.

Thank you,
Doug

 

[D-Fresh]

Sure, no problem... Just add a quick subroutine in the BeforeUpdate event of the company name textbox...

private sub companyname_Beforeupdate(cancel as integer)

dim ctr as integer
dim IsAsterisk as boolean

IsAsterisk = false

for ctr = 1 to len(me!CompanyName)
if mid(me!CompanyName,ctr,1) then IsAsterisk = true
next

if not IsAsterisk then
msgbox ("Sorry, you must have an * in the company name for alphabatizing purposes...")
cancel = true
end if

end sub

This will loop through the name and check for the asterisk. IF it finds it, it will set the IsAsterisk flag to true. Afterwards, if IsAsterisk is false, then it will let the user know and cancel the update. Hope that helps.

Doug

 

[DALeffler]

Why not use the Instr function?

private sub companyname_Beforeupdate(cancel as integer)

dim ctr as integer
dim IsAsterisk as boolean

IsAsterisk = false

for ctr = 1 to len(me!CompanyName)
if mid(me!CompanyName,ctr,1) then IsAsterisk = true
next

if not IsAsterisk then
msgbox ("Sorry, you must have an * in the company name for alphabatizing purposes...")
cancel = true
end if

end sub

becomes:

Option Compare Database
____________________________________

private sub companyname_Beforeupdate(cancel as integer)

If Instr(1,me!CompanyName,"*") = 0 Then
msgbox ("Sorry, you must have an * in the company name for alphabatizing purposes...")
cancel = true
end if

end sub

Doug (Sorry to nitpick)

3 Dougs on one thread - that's a first for me!!!

[This message has been edited by DALeffler (edited 06-21-2001).]

 

[D-Fresh]

Good suggestion... No nitpicking there.. Just a better answer... Thanks...

Doug #2

 

[Atomic Shrimp]

May I recommend (if it's not already too late) choosing a different character than * ?

It's just that * is a wildcard character and might cause you problems later on if you try searching for it in a query or something.

My suggestion would be to use the Underscore_ or hat^ character.