find missing numbers (Sort of)

[Guest]

What I truly want is to list all numbers from a field into a single field on
a report.

I have a table/query that lists check numbers and other info. Once a month
I generate a report that must list the check numbers used. The problem is
the numbers are not always sequential. I need a way to return these numbers
in a format like (1-7, 11, 15-20) or similar. Is there a way I could
possibly do this?

 

[Douglas J Steele]

I showed a few approaches in my April 2004 Access Answers column in Pinnacle
Publication's Smart Access (it's the "Mind the Gap" section). Note, though,
that I didn't extend the solutions to return the missing numbers in the
exact format that you posted.

You can download the column (and accompanying sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[John Vinson]

What I truly want is to list all numbers from a field into a single field on
a report.

I have a table/query that lists check numbers and other info. Once a month
I generate a report that must list the check numbers used. The problem is
the numbers are not always sequential. I need a way to return these numbers
in a format like (1-7, 11, 15-20) or similar. Is there a way I could
possibly do this?

You can use the VBA code at

http://www.mvps.org/access/modules/mdl0004.htm

to get a string "1, 2, 3, 4, 5, 6, 7, 11, 15, ..."

You'ld need to do some checking within the code to get the hyphenated
ranges, but it should at least give you a starting point.

John W. Vinson[MVP]

 

[Douglas J Steele]

Just realized I probably misread your question. What I've got is how to
identify missing numbers (which is what your subject says). However, it
sounds as though you're trying to return what numbers are there, not the
missing ones (although I suppose solving one problem will provide the
solution to the other one...)