Separating field data with special criteria

[Falcon88]

Hello everyone

I have a field to store the insurance card number according to the companies. Each insurance company has a special pattern in numbering its subscribers' cards as follows:

1. Company A

Yyy*xxxxxxxx
Yy*xxxxxxxxx
Y*xxxxxxx

2. Company B

Xxxxxxx*y
Xxxxxxx*yyy
Xxxxxxxx*yyy

3. Company C
Xxxxxxxx
Xxxxx
Xxxxxxx

And so on

Other details

Y: is the policy number

X: is the subscriber's card number

Note that the numbers y often range between 1 and 3 digits only

While the numbers x range between 4 and more than ten digits

What is required is to separate the numbers into two independent fields

1. The policy

2. The subscriber's card number

 

[MajP]

You can write a module to do this by passing in the number and the company and then have a rule for each company. However this might not be sustainable if you have lots of companies that grow over time. Because then the code has to be continually updated.
If it is lots of companies with changing rules you would then want some table solution where you maintain the rules in the table so you can add, edit, and delete rules. But then it is real tricky as to how you create the table to be able to interpret the table rules in code.

How many Companies are you talking? Is the asterisk really an asterisk? Are these real example rules or notional?

This should work for your example

Public Enum insurancePart
  PolicyNo = 1
  SubcriberNo = 2
End Enum

Public Function GetPart(CompanyID As Long, InsuranceCardNumber As String, PolicyOrSubscriber As insurancePart) As String
  Select Case CompanyID
    Case 1
      If PolicyOrSubscriber = PolicyNo Then
        GetPart = Split(InsuranceCardNumber, "*")(0)
      Else
        GetPart = Split(InsuranceCardNumber, "*")(1)
      End If
    Case 2
      If PolicyOrSubscriber = PolicyNo Then
        GetPart = Split(InsuranceCardNumber, "*")(1)
      Else
        GetPart = Split(InsuranceCardNumber, "*")(0)
      End If
   Case 3
       If PolicyOrSubscriber = PolicyNo Then
        GetPart = "None"
      Else
        GetPart = InsuranceCardNumber
      End If
  End Select
End Function

if A has companyID of 1, B 2, and C 3.
Then if you ask for policyNo for 1 (A) you get the Y part and if you as for SubscriberNo you get the X part. Same for the other cases. I interpreted C to only having a subscriber number since no y part.
Again this may not be maintainable if lots of companies unless lots of companies have the same rules.

 

[CJ_London]

depends on where you are going to use it but I would create for each company a format string to indicate where each part can be found. e.g.

XXXYYYYY
XXYYYYYYYY
YYYXXXXX

Providing the first part does not vary in length by insurance company then this function would work. - You'll need to finish it off depending on how you want the values returned - (to populate a control? in a query?, update a table? etc

Function splitStr(CoStr As String, srcStr As String) As Boolean
'coStr - the insurance company format, srctStr - the string you want to analyse
    retPol = Mid(srcStr, InStr(CoStr, "Y"), InStrRev(CoStr, "Y"))
    retSub = Mid(srcStr, InStr(CoStr, "X"), InStrRev(CoStr, "X"))
    
    Debug.Print "Pol: " & retPol, "Sub: " & retSub

End Function

in use
splitstr "YYXXXXX","1234567"
Pol: 12 Sub: 34567

splitstr "YYXX","1234567"
Pol: 12 Sub: 3456

splitstr "XXXYY","1234567"
Pol: 4567 Sub: 123

Doesn't matter if there are characters, since it works on position

splitstr "XXXYY","1234567A"
Pol: 4567A Sub: 123

If the asterix needs to be removed, use the replace function