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

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

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

4. ## 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)

5. ## 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. ## 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;