Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

uncleG (TechnicalUser) (OP)
4 Dec 08 12:47
Hi All, Once again I am stumped. Yes looking for direction.
Legacy data and a few customers prevents using an LI for Serial Numbers

1) I would like to create a module or procedure to tell me which serial numbers are missing.
This is this origin of the SN List which is passed from Labview via ODBC, the SN can be either Text or LI.

SELECT DISTINCT qryDataRaw2L.final_sn
FROM qryDataRaw2L
WHERE (((qryDataRaw2L.final_sn) Between [Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text362] And
ORDER BY qryDataRaw2L.final_sn;

[Text362] is the Min in List
[Text363] is the Max in List
[final_sn]  is a text field

2) I would also like to be able to pass both the Pass and Fail lists formated as comma seperated into a memo field for later export such as:

SELECT DISTINCT qryDataRaw2L.final_sn, qryDataRaw2L.PassFail
FROM qryDataRaw2L
WHERE (((qryDataRaw2L.final_sn) Between [Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text362] And
[Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text363]) AND ((qryDataRaw2L.PassFail)="Pass"))
ORDER BY qryDataRaw2L.final_sn;

Desired Output: [snPassList] = 94352-94362, 94366-94404, 94406-94527

SELECT DISTINCT qryDataRaw2L.final_sn, qryDataRaw2L.PassFail
FROM qryDataRaw2L
WHERE (((qryDataRaw2L.final_sn) Between [Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text362] And

[Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text363]) AND ((qryDataRaw2L.PassFail)="Fail"))
ORDER BY qryDataRaw2L.final_sn;

Desired Output: [snFailList] = 94363-94365, 94405

Thanks Again,
SkipVought (Programmer)
4 Dec 08 20:12

What application?

What database tool are you using?


glassesJust traded in my old subtlety...
for a NUANCE!tongue

SkipVought (Programmer)
4 Dec 08 20:20
Sorry, I thought I was in a different forum. blush


glassesJust traded in my old subtlety...
for a NUANCE!tongue

uncleG (TechnicalUser) (OP)
5 Dec 08 6:55
I am using Access 2003
Helpful Member!  MajP (TechnicalUser)
5 Dec 08 10:06
What do you mean by missing serial? Can you explain the desired output?  Not sure what that means.
94352-94362, 94366-94404, 94406-94527

Are you saying you have serials like


and you want the missing serial to be
 94354,94355, 94356
but write the output like
 94354 - 95356

Or does 94354,94355, 94356 exist in another list.

If the values do not exist you will have to write code.  If the values exist, but are not returned by your query you can use Sql.

If the sequence is not numerical the code get more complicated


The missing serials above would be based on a complicated buisness rule.
uncleG (TechnicalUser) (OP)
5 Dec 08 16:07
Thanks for the response MajP,
Some answers to your questions, maybe?

The complete set of numbers do not exist in another list, the list would need to be built in code first using the min and the max on the form. At this point I am trying to verify that all products were indeed tested.(no missing serial numbers unless the product failed in an earlier test, who's results are on paper.)
Next the existing SN's would need to be compared to the number range and a list produced of the missing numbers.
(this would tell me who is missing)Counting records doesn't work because sometimes the product is tested 2, 3 or 6 times times for any number of reasons.
At this stage it would be fine as a simple column listing the individuals not present in the group. This will occur as part of the batch review going forward. I continue to refer to the SN's as numbers but they are text.
There are both legacy records and customer supplied serial numbers which are in fact text and numbers mixed, which also has the potential to create duplicate SN's however they are isolated via a Work Order and Batch Number. These records exist in several old programs and we are attempting to bring them out into one table, and there have been some dupicates but not of the same product. I am considering using a number field to reassign a key to provide structure and use the old SN (Text) as a name for cross reference. Once out I can normalize the structure.

The Dash Comma Format Thing:
Just something I have been kicking around.
Find the numbers missing in a group and produce the following output format as opposed to listing each individual.
The output Like "Desired Output: [snPassList] = 94352-94362, 94366-94404, 94406-94527"  is the format we use on a certificate which is shipped with the product, looks like a stupid diploma. At this point in time the SN list is complete but the products which failed inspection are not in the list so there are gaps which is why they use the comma and dashes. I've played with a few things here but have gotten nowhere and deleted those attempts. By the time I get through this one I should have a good understanding of looping.
Thanks for your interest,
MajP (TechnicalUser)
7 Dec 08 23:57
Your example shows numeric values not alpha numeric values.  If they are numeric values saved as text the solution is pretty easy.  If they are alpha numeric values, there is no generic algorithm that anyone can write.  Because you would have to sequence the values based on your buisness rules.  For example
What is the next value in the sequence?
  Ab123xz or Ab124xy or ac123xy or bb123xy or something else
Depends on your rules. This will really complicate the problem.  It is doable, but will be very complicated.

If I had numeric values that had a natural sequence. I would build a function that returns the sequence as an array.  This gives you the flexible to print the array, or save it to a table.

here is my data of items tested


ID    final_sn
1    94353
2    94351
3    94351
4    94353
5    94357
6    94357
7    94369
8    94366
9    94367
10    94363
11    94369
12    94370
13    94371
14    94370
15    94372
I sort them and select unique values in "qrySNList"



then build a function where I can pass in the min and max range.  These values can come from a form's fields. I also pass in the name of the table/query and the field name.


Public Function getMissingSN(strDomain As String, strFld As String, minVal As Variant, maxVal As Variant) As Long()
  Dim rs As DAO.Recordset
  Dim missingVal As Long
  Dim aMissingVals() As Long
  Dim intcounter As Integer
  Set rs = CurrentDb.OpenRecordset(strDomain, dbOpenDynaset)
  ReDim aMissingVals((maxVal - minVal) + 1)
  For missingVal = minVal To maxVal
    rs.FindFirst strFld & " = '" & missingVal & " '"
    If rs.NoMatch Then
      aMissingVals(intcounter) = missingVal
      intcounter = intcounter + 1
    End If
  Next missingVal
  If aMissingVals(intcounter) = 0 Then
    intcounter = intcounter - 1
  End If
  ReDim Preserve aMissingVals(intcounter)

  getMissingSN = aMissingVals
End Function
I test this function


Public Sub testMissing()
  Dim vals() As Long
  Dim intcounter As Integer
  vals = getMissingSN("qrySNList", "final_sn", "94354", "94370")
  For intcounter = LBound(vals) To UBound(vals)
      Debug.Print vals(intcounter)
    Next intcounter

End Sub

You could save these values to a table.  The result is



Changing this into the "beginValue - endValue" format will be a pain.  It is doable, but will take some work.  You have to read through the array checking to see if the value is more than one greater than the previous value.  If it is you can concatenate it as the "endValue". If not you have to check the next value. Until you find a value more than 1 greater than the previous or you reach the end of the array.  If you find an end value then the next value is you "beginValue".
uncleG (TechnicalUser) (OP)
8 Dec 08 7:22
Thanks MajP,
This logic will work going forward, the function is exactly what I was looking for. As far as a Business Rule goes it had been a free for all, but I have been tasked with changing that.
Thanks for your help,
MajP (TechnicalUser)
8 Dec 08 9:03
If you are using an alpha numeric you would modify the code


  For missingVal = minVal To maxVal
    rs.FindFirst strFld & " = '" & missingVal & " '"
    If rs.NoMatch Then
      aMissingVals(intcounter) = missingVal
      intcounter = intcounter + 1
    End If
  Next missingVal

to something like


dim firstPass as boolean
firstPass = true
missingVal = minVal
  if not firstPass then
    missingVal = nextVal(missingVal)
  end if
  rs.FindFirst strFld & " = '" & missingVal & " '"
    If rs.NoMatch Then
      aMissingVals(intcounter) = missingVal
      intcounter = intcounter + 1
   End If
   firstPass = false
loop until maxVal = missingVal


  public function nextVal(currentVal as string) as string
    ' need some code here to iterate your series
    ' based on your rules
    ' so if you pass in 'abc123xy'
    ' it will return the next value like 'abc123xz'
    nextVal =
  end function
uncleG (TechnicalUser) (OP)
8 Dec 08 10:23
Hi MajP,
I will play with the alpha numeric as well later in the week after we settle (hopefully) on a new format.
Thanks Again and have a Happy Holiday season,

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close