Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query to Flag Gap in Number Sequence? (A2k (9.0.3821) SR-1)

    Query to Flag Gap in Number Sequence?

    A2k (9.0.3821) SR-1

    If I have a query the returns the following in permit number sequence:

    Permit # Name
    2002-00001 Tom Brown
    2002-00002 Joe Black
    2002-00004 Bill Jones
    2002-00005 Dick Smith

    Is there anyway to generate an asterisk (*) either above or below the missing permit number, in this case 2002-00003?

    Permit # Name Flag
    2002-00001 Tom Brown
    2002-00002 Joe Black *
    2002-00004 Bill Jones *
    2002-00005 Dick Smith

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to Flag Gap in Number Sequence? (A2k (9.0.3821) SR-1)

    There is no gap between Tom Brown and Joe Black and no gap between Bill Jones and Dick Smith, but your second example shows an asterisk next to both Joe Black and Bill Jones. I'd go for Bill Jones to indicate a gap had preceded that record.

    You could use a formula in your query to calculate the previous record (i.e., a DMax of PermitNumber < the current record's permit number) and test to see if that is more than the current permit number -1, but that will break down if you cross years.

    Is permit number a string or do you keep the parts in separate fields and format it for this purpose?
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Flag Gap in Number Sequence? (A2k (9.0.3821) SR-1)

    Hi Charoltte

    Thanks for getting me started.

    Code is working OK, however, how can I make more efficient by setting up static variables and only doing dmin & dmax once

    Thanks, John


    'Call from query with Gap: CheckForGapInYearSequenceNo([strPermitNo],"strPermitNo","tblBPermit")
    Public Function CheckForGapInYearSequenceNo(strFieldValue As String, _
    strFieldName As String, strTableName As String) As String

    ' this code will return an * before and after the missing sequence number
    ' stored in strFieldValue is a text field, format is yyyy-nnnnn,
    ' where yyyy is current year & nnnnn is continous sequence number

    Dim lngSeqNo As Long
    Dim lngMinSeqNo As Long
    Dim lngMaxSeqNo As Long
    Dim strCriteria As String

    lngSeqNo = Right(strFieldValue, 5)
    lngMinSeqNo = Right(DMin(strFieldName, strTableName), 5)
    lngMaxSeqNo = Right(DMax(strFieldName, strTableName), 5)

    If lngSeqNo <> lngMinSeqNo Then ' don't check first number
    strCriteria = Left(strFieldValue, 5)
    strCriteria = strCriteria & Format(lngSeqNo - 1, "00000")
    ' check for existence of previous number
    If IsNull(DLookup("[" & strFieldName & "]", "[" & strTableName & "]", _
    "[" & strFieldName & "] = '" & strCriteria & "'")) Then
    CheckForGapInYearSequenceNo = "*"
    Exit Function
    End If
    End If

    strCriteria = Left(strFieldValue, 5)
    strCriteria = strCriteria & Format(lngSeqNo + 1, "00000")
    ' check for existence of next number
    If IsNull(DLookup("[" & strFieldName & "]", "[" & strTableName & "]", _
    "[" & strFieldName & "] = '" & strCriteria & "'")) Then
    If lngSeqNo < lngMaxSeqNo Then ' don't check last number
    CheckForGapInYearSequenceNo = "*"
    End If
    End If
    End Function
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to Flag Gap in Number Sequence? (A2k (9.0.3821) SR-1)

    Sorry, John. You lost me on that last curve. I thought you were trying to do this in a query, which is where the DMAx made sense.

    A simple DMax in the query would be <pre>DMax("[PermitNo]","tblBPermit","[PermitNo]< '" & [PermitNo & "'")</pre>

    This assumes that the field is named PermitNo and it is a string value. You could then compare it by comparing the last few digits + 1 to the current permit no to see if they're the same. If not, return an asterisk.

    I'm not quite sure what you're doing with these statements or why:

    lngMinSeqNo = Right(DMin(strFieldName, strTableName), 5)
    lngMaxSeqNo = Right(DMax(strFieldName, strTableName), 5)
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to Flag Gap in Number Sequence? (A2k (9.0.3821) SR-1)

    John,

    Based on Charlottes remarks, you can create a query detecting gaps without using VBA.

    Here is the SQL of such a query:

    SELECT tblBPermit.strPermitNo, Val(Right(Nz(DMax("strPermitNo","tblBPermit","strP ermitNo<'" & [strPermitNo] & "'")),5))<>(Val(Right([strPermitNo],5))-1) AS IsGap, IIf([IsGap],"*","") AS Gap
    FROM tblBPermit;

    Take good note of the placement of single and double quotes. In particular, the condition argument to DMax is
    <pre>"strPermitNo<'" & [strPermitNo] & "'"</pre>


    The idea is:
    Get the max of strPermitNo in previous records.
    Convert the last 5 character to numeric. Use Nz to prevent error on first record.
    Also compute the numeric value of the last 5 character of the present record.
    See if they differ by one.

    It is possible to combine IsGap and Gap into one field iff you like; I kept them separate because the IsGap field can be used for calculations more easily then Gap.

    Regards,
    Hans

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to Flag Gap in Number Sequence? (A2k (9.0.3821) SR-1)

    Hi Hans

    Wow! what a piece of code.

    I modified it as follows to flag both the before and after missing record and it works OK, however i'm missing something because it is flagging the last record in the query

    What am I missing?

    SELECT tblBPermit.strPermitNo, Val(Right(Nz(DMin("strPermitNo","tblBPermit","strP ermitNo>'" & [strPermitNo] & "'")),5))<>(Val(Right([strPermitNo],5))+1) AS [IsGap Before], Val(Right(Nz(DMax("strPermitNo","tblBPermit","strP ermitNo<'" & [strPermitNo] & "'")),5))<>(Val(Right([strPermitNo],5))-1) AS [IsGap After], IIf([IsGap After],"*",IIf([IsGap Before],"*","")) AS Gap
    FROM tblBPermit
    ORDER BY tblBPermit.strPermitNo;

    Thanks for your help.

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •