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

1. ## 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

2. ## 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. ## 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. ## 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
•