Results 1 to 4 of 4
  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

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

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

    Thanks to HansV I
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    Try this:

    SELECT [tblBPermit].[strPermitNo], DMin("strPermitNo","tblBPermit","strPermitNo>'" & [strPermitNo] & "'") as DMinNo,DMax("strPermitNo","tblBPermit","strPermitN o<'" & [strPermitNo] & "'") as DMaxNo,
    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] and not isnull(DMinNo),"*","")) AS Gap
    FROM tblBPermit
    ORDER BY [tblBPermit].[strPermitNo];

    Cheers,
    Pat

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

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

    Pat's suggestion is excellent. Just in case your numbers start with -00000, you might enhance it one step further to prevent having the first record incorrectly flagged:

    SELECT tblBPermit.strPermitNo, DMin("strPermitNo","tblBPermit","strPermitNo>'" & [strPermitNo] & "'") AS DMinNo, DMax("strPermitNo","tblBPermit","strPermitNo<'" & [strPermitNo] & "'") AS DMaxNo, 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] And Not IsNull([DMaxNo]),"*",IIf([IsGap Before] And Not IsNull([DMinNo]),"*","")) AS Gap
    FROM tblBPermit
    ORDER BY tblBPermit.strPermitNo;

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

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

    Hi Pat & Hans

    Thanks for the code, looks almost bullet proof at this point, it's going to help my clients keep their numbers in sequence.

    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
  •