generate a unique reference using extracted letters

[Ali29J]

Hi all

I am lookin to generate a unique reference which uses the first 2 letters and last letter of the customer name, for example: customer name - British Gas, the code would extract BRS, and insert into a reference number.

Can anyone offer any assistance, the rest of the code i have is:

Me.ReportRefNo.Value = "VR-" & Me.ReportID

I would like the letters to come between "VR-" and the report ID

I would also like the ReportID to be entered in the format 0001 if the number is 1

would appreciate some guidance

THanks

ALi

 

[Remou]

You can use Left and Right on the name. Watch out for O'Neill and D'Arcy and so on. You may wish to consider what to do with Plc and Ltd, etc.

Left(Trim(CompanyName),2)
Right(Trim(CompanyName),1)
Format(ReportNo,"0000")

 

[Ali29J]

Hi Remou

Many thanks, so I ve got the following works great

Me.ReportRefNo.Value = "VR-" & Left(Trim(Text19), 2) & Right(Trim(Text19), 1) & Format(Me.ReportID, "0000")

Me("ReportRefNo") = UCase(Me("ReportRefNo"))

Your comment on Plc and Ltd noted, how could get round this?? some form of IF statement??

 

[Remou]

I think you could use an exceptions list and a small function to create your number. You will need to examine your customers to see what comes up.

Function GetNo(CoName,RNo)
'Typed, not tested
astrExcept = Split("Ltd,Plc,Co",",")

For i=0 To Ubound(astrExcept)
   If Trim(Mid(CoName,InstrRev(CoName," ")))=astrExcept(i) then
      CoName=Trim(Mid(CoName,1,InstrRev(CoName," ")))
   End If
Next

GetNo="VR-" & Left(CoName, 2) & Right(CoName, 1) & Format(RNo, "0000")

Or there abouts.

However, you may wish to consider using the first three letters of the company instead of yhe first two and last one. This would also provide an easy search cue.

 

[Ali29J]

hmmm think your probably right no use causing more work that necessary!

thanks for your assistance

ALi