Determine remaining values from input like 1,2,3-10,11,13-15

[sdl2]

I think this may be a very hard problem. I have a database which has a part number and that part number has a certain number of holes. This value is known but can range from 0 to 200. These holes get springs based on input from the user. For example, the part has 15 holes


Spring 1: 1,2,3-10
Spring 2: 11
Spring 3:
Spring 4: 13-15
Spring 5:

I am looking for a way to input this in access so that access recognizes that 3-10 means 3,4,5,6,7,8,9,10.
I need it to be able to tell what positions are missing or duplicated. In this example 12 is missing.

Please help or suggest alternatives. Typing 1,2,3,4,200 10x a day is not going to work and is very prone to errors. A list can not work either as 25 positions has choose 13 has over 5,000,000 combinations

Thanks

[orange]

So, trying to fill in the description, you seem to have:

An unknown number of Parts or Pieces each of which may have from 0 to 200 holes. The holes on a Part/Piece are identifiable. And you have a finite number of Springs or types of Springs that may be assigned to 0 or more holes in any Part/Piece.

[sdl2]

So, trying to fill in the description, you seem to have:

An unknown number of Parts or Pieces each of which may have from 0 to 200 holes. The holes on a Part/Piece are identifiable. And you have a finite number of Springs or types of Springs that may be assigned to 0 or more holes in any Part/Piece.

Yes orange, that is correct. There are 5 possible springs, not all are required. Each record has 1 part and on that record the spring information needs to be filled in. I know how many holes each part has, that information is stored in a field.

[orange]

I am attaching a draft data model that may be useful to you as a starting point.
If it isn't applicable, then please ignore it.
Good luck with your project.

[sdl2]

I am attaching a draft data model that may be useful to you as a starting point.
If it isn't applicable, then please ignore it.
Good luck with your project.

Hey orange,
Thank you for this, do you have any idea about how to approach the calculation. Let's just assume 11 holes. To translate a user input 1,3-10,11 for access to interpret it as 1,3,4,5,6,7,8,9,10,11 and realize that position 2 is missing? This is where the struggle is, how to accomplish this.

[orange]

You could try to adapt a Select Case construct.
http://www.techonthenet.com/access/f...anced/case.php

For testValue = 1 to 11
  Select Case 
  Case 1
  'do whatever for case1 perhaps assign Spring100
   Case 3 to 10
  'do whatever for case3,10 perhaps assign Spring105
  Case 11
  'do whatever for case11 perhaps assign Spring110
  Case Else
  'This could pick up the numbers not explicitly identified
  '  for example you could test values from 1 to your Max eg 11
    'this would identify 2 as missing
    debug.print testValue

    End Select 
Next TestValue

If you have a few test cases, then perhaps a user defined function could be built. Need more details/examples.

[CJ_London]

providing your users are consistent in their input - i.e. always separate values with a comma and no spaces either side of the '-' (although you could resolve those in code) you could manipulate and array

for a user input 1,3-10,11

function ExpandNumbers(input as string) as string
dim Arr1() as string
dim Arr2() as integer
dim I as integer
dim j as integer
dim k as integer

    arr1=split(input) 'to divide into separate elements
    j=0
    for i = 0 to ubound(Arr1)-1 'step through each element
         if instr(arr1(I),"-")=0 then 'just a number
             arr2(j)=val(arr1(I))
             j=j+1
         else 'there is a range of numbers
             arr2(j)=val(arr1(I))
             k=arr2(j)+1
             j=j+1
             while k<=val(mid(arr1(I),instr(arr1(I),"-")+1)
                  arr2(j)=k
                  j=j+1
                  k=k+1
             wend

        end if
    next I

    ExpandNumbers=""
    for I=0 to ubound(arr2)-1
        ExpandNumbers=ExpandNumbers & arr2(I) & ","
    next I
    
    ExpandNumbers=left(ExpandNumbers,len(ExpandNumbers)-1)

end function