Compare Query

[dmullins]

I have a table that we assign license numbers to and enter them in the table. Over the years many numbers have been missed and skipped. So I made a table that compares sequentially and then looks at the ones missing as the next number. The problem is they can begin with MB or MBB with a total of 8 characters, ie (MB0001221 or MBB001221), only care about the numbers.
My comparison is the problem, I don't know how to compare just the numbers. They will be sequential no matter if mb or mbb they will never be the same number even if different starting aplha.

Please help

 

[ajetrumpet]

whatever you're doing to compare them should remain the same. and to extract just the numbers from the right hand side of the string, call a function off of the field where the string is. portion of the sql needed:

select GetJustNums([fieldname])

code to call:

function getJustNums(fld as string) as string()

dim i as integer

for i=1 to len(fld)
  if isnumeric(mid(fld, i, 1)) then
    getJustNums=right(fld, ((len(fld)-i)+1))
      exit function
  end if
next i

getJustNums="INVALID LICENSE NUMBER"

end function

 

[MSAccessRookie]

I have a table that we assign license numbers to and enter them in the table. Over the years many numbers have been missed and skipped. So I made a table that compares sequentially and then looks at the ones missing as the next number. The problem is they can begin with MB or MBB with a total of 8 characters, ie (MB0001221 or MBB001221), only care about the numbers.
My comparison is the problem, I don't know how to compare just the numbers. They will be sequential no matter if mb or mbb they will never be the same number even if different starting aplha.

Please help

For a quick work around, take a look at the Replace() Function. As long as the lead characters are EXACTLY MB or MBB, you could elmininate them by replacing them with null strings. Of course, if other characters are being used, then this would not work. Something like the following:

Replace(Replace(YourString, "M", ""), "B", "")

I am also sure that with a little more effort, you could find a way to eliminate other letters and leave behind only the numbers.

 

[dmullins]

Got it, and thanks