当数字存储在短文本字段中时，MS Access Query 查找序列号中的间隙 [英] MS Access Query to find gaps in sequential numbers when numbers are stored in Short Text field

问题描述

我有一个表 (tblParts)，其中有一个 PartNumber 字段(Short Text)，其中存储了属于多个系列的零件的 6 位零件编号.系列由部件号的前 2 位数字(00、01、02 等)表示.(注意:我没有创建这个表，目前无法更改)

I have a table (tblParts) with a PartNumber field (Short Text) which stores 6 digit part numbers for parts belonging to several families. The families are denoted by the first 2 digits of the part number (00, 01, 02, etc). (NOTE: I did not create this table and am not able to change it at this time)

我需要找到编号中的空白以填充未使用的部件号.如果我的项目开始时需要特定系列中的 6 个连续零件编号，我想在该系列中该尺寸或更大的第一个间隙中找到第一个未使用的编号.

I need to find gaps in the numbering in order to fill in unused part numbers. If I have a project starting that needs 6 consecutive part numbers in a specific family, I want to find the first unused number in the first gap of that size or greater within that family.

这是数据的一小部分.

PartNumber
020001
020002
020003
020004
020005
020006
020007
020009
020010
020011
020012
020013
020014
020019
020101

如果我需要一个数字，查询应该找到 020008.如果我需要 3 个数字，它应该找到 0200015，如果我需要 10 个数字，它应该找到 020020.

If I needed a single number, the query should find 020008. If I needed 3 numbers, it should find 0200015 and if I needed 10 numbers it should find 020020.

我的 SQL 知识非常有限，但我正在努力学习.我意识到如果信息存储得当，这会容易得多，但我无法控制它.

My SQL knowledge is very limited but I am trying to learn. I realize this would be much easier if the information was stored properly but I have no control over it.

推荐答案

我曾经写过一篇关于这个主题的文章:

I once wrote an article on the subject:

查找并生成访问表中的缺失值

但这将填补所有空白，直到建立所有新数字.因此，该代码需要使用外循环进行扩展，以确保始终并列数字.

but that will fill up any gap until all new numbers were established. So, that code will need an expansion with an outer loop to ensure juxtaposed numbers at all times.

Private Sub btnSearch_Click()

' Read table/query sequentially to 
' record all missing IDs.
' Fill a ListBox with missing values.
' A reference to Microsoft DAO must be 
' present.

  ' Define search table or query.
  Const cstrTable As String = "Orders"
  Const cstrField As String = "OrderID"

  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim lst As ListBox
  Dim col As Collection

  Dim strSQL As String
  Dim strList As String
  Dim lngLast As Long
  Dim lngNext As Long
  Dim lngMiss As Long

  strSQL = "Select " & cstrField & "" _
   & " From " & cstrTable & _
   & " Order By 1;"  

  Set lst = Me!lstMissing
  Set col = New Collection
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSQL)

  If rst.RecordCount = 0 Then
    'The recordset is empty.
    'Nothing to do.
  Else
    lngLast = rst(cstrField).Value
    rst.MoveNext
    While rst.EOF = False
      lngNext = rst(cstrField).Value
      For lngMiss = lngLast + 1 To _
       lngNext - 1
        col.Add (lngMiss)
      Next
      lngLast = lngNext
      rst.MoveNext
    Wend
      'Generate next value in sequence.
      'Discard if collecting only 
      'missing values.
      col.Add (lngLast + 1)
  End If
  rst.Close

  'Populate list box from collection.
  For lngMiss = 1 To col.Count
    If Len(strList) > 0 Then 
      strList = strList & ";"
    End If
    strList = strList & col(lngMiss)
    Debug.Print col(lngMiss)
  Next
  lst.RowSource = strList
  Debug.Print strList

  Set rst = Nothing
  Set dbs = Nothing
  Set col = Nothing
  Set lst = Nothing

End Sub

这篇关于当数字存储在短文本字段中时，MS Access Query 查找序列号中的间隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！