Present the 'gaps' in numbering

[Arno R]

I need to find the 'gaps' in a numbered recordset like:
1 Philips
2 Jones
5 Jo Do
6 Frenzy
10 John Matthias
23 Arno R (and so on ....)

What I need is one blank line (or a line that says: " ... gap here ...." ) everytime a gap is found like:
1 Mr. Philips
2 Mrs. Jones

5 Jo Do
6 Frenzy

10 John Matthias

23 Arno R

What would be the most simple way to achieve this ?
I can do it by creating or using a temptable, but can it be done without the temptable?

Thanks
Arno R

[dkintheuk]

Hey pal,

If you to a loop for the max(recordsetCounter) and then check for a
valid line for each loop run through, where you don't find a record,
just return a gap here message into an output string.

I suppose it depends what you intend to do with the result set.

If you want a query to do this then i would use a code step to create a
recordset based on the max(recordsetCounter) and make a table of this
(basically a list of records containing only one field that is
effectively a 1 to n row autonumber.

Then you can create a query that links the two recordsets together and
using the correct join... left inner i think... you can return every
row and where null returns from the current recordset, you can dsiplay
yhe correct message.

Can you tell i'm sleepy and can't be arsed to write out code or
query...

Hope this helps you,

Rob.

Arno R wrote:

I need to find the 'gaps' in a numbered recordset like:
1 Philips
2 Jones
5 Jo Do
6 Frenzy
10 John Matthias
23 Arno R (and so on ....)

What I need is one blank line (or a line that says: " ... gap here ..." ) everytime a gap is found like:
1 Mr. Philips
2 Mrs. Jones

5 Jo Do
6 Frenzy

10 John Matthias

23 Arno R

What would be the most simple way to achieve this ?
I can do it by creating or using a temptable, but can it be done without the temptable?

Thanks
Arno R

[Douglas J. Steele]

Take a look at the "Mind the gap" section of my April 2004 Access Answers
column in Pinnacle Publication's Smart Access. You can download the column
(and accompanying sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Arno R" <ar***********@tiscali.nl> wrote in message
news:42*********************@dreader2.news.tiscali .nl...
I need to find the 'gaps' in a numbered recordset like:
1 Philips
2 Jones
5 Jo Do
6 Frenzy
10 John Matthias
23 Arno R (and so on ....)

What I need is one blank line (or a line that says: " ... gap here ..." )
everytime a gap is found like:
1 Mr. Philips
2 Mrs. Jones

5 Jo Do
6 Frenzy

10 John Matthias

23 Arno R

What would be the most simple way to achieve this ?
I can do it by creating or using a temptable, but can it be done without the
temptable?

Thanks
Arno R

[Thelma Lubkin]

Arno R <ar***********@tiscali.nl> wrote:
: I need to find the 'gaps' in a numbered recordset like:
: 1 Philips
: 2 Jones
: 5 Jo Do
: 6 Frenzy
: 10 John Matthias
: 23 Arno R (and so on ....)

: What I need is one blank line (or a line that says: " ... gap here ..." ) everytime a gap is found like:
: 1 Mr. Philips
: 2 Mrs. Jones

: 5 Jo Do
: 6 Frenzy

: 10 John Matthias

: 23 Arno R

: What would be the most simple way to achieve this ?
: I can do it by creating or using a temptable, but can it be done without the temptable?
I'd use a function like this:

dim prevrec as variant
dim gapcnt as integer

prevrec = first_recnum-1
gapcnt = 0

integer function fixgap(recnum as variant)

fixgap = 0

if recnum <> prevrec+1 then
gapcnt = gapcnt+1
else
if gapcnt > 0 then
fixgap = 1
end if

prevrec = recnum

end function

You'd need to figure out where to call the function and where to
define prevrec and gapcnt.
....but then again I *always* try to use code if I can get away w/ it.
--thelma
: Thanks
: Arno R

[Arno R]

Nice article Doug, very helpful on the issue.
I will go for the strmsg-approach as I only need to 'present' the gaps (the first 5 perhaps)
Also the 'range-query' is very interesting!

Thanks, (also to Thelma and Rob)

Arno R

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht news:XM********************@rogers.com...

Take a look at the "Mind the gap" section of my April 2004 Access Answers
column in Pinnacle Publication's Smart Access. You can download the column
(and accompanying sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Arno R" <ar***********@tiscali.nl> wrote in message
news:42*********************@dreader2.news.tiscali .nl...
I need to find the 'gaps' in a numbered recordset like:
1 Philips
2 Jones
5 Jo Do
6 Frenzy
10 John Matthias
23 Arno R (and so on ....)

What I need is one blank line (or a line that says: " ... gap here ...." )
everytime a gap is found like:
1 Mr. Philips
2 Mrs. Jones

5 Jo Do
6 Frenzy

10 John Matthias

23 Arno R

What would be the most simple way to achieve this ?
I can do it by creating or using a temptable, but can it be done without the
temptable?

Thanks
Arno R

[corey lawson]

"Arno R" <ar***********@tiscali.nl> wrote in
news:42*********************@dreader2.news.tiscali .nl:

I need to find the 'gaps' in a numbered recordset like:
1 Philips
2 Jones
5 Jo Do
6 Frenzy
10 John Matthias
23 Arno R (and so on ....)

What I need is one blank line (or a line that says: " ... gap here
..." ) everytime a gap is found like: 1 Mr. Philips
2 Mrs. Jones

5 Jo Do
6 Frenzy

10 John Matthias

23 Arno R

What would be the most simple way to achieve this ?
I can do it by creating or using a temptable, but can it be done
without the temptable?

Thanks
Arno R

Well, if you do this in a report, then...
select md.*, md1.id, md1.id as next_id
from MyData as MD left join MyData as MD1 on
md.id = (md1.id-1)

That should give you this set:

id, name, next_id
1, ..., 2
2, ..., <null>
5, ...., 6
6, ..., <null>
10,..., <null>
15

....and so on.

In a report, then, you want to add another control BELOW like this:

[detail=========================]
<id > <name ><nextid >
<nullplaceholder>

The <nullplaceholder> control should be a Text box that is bound to this
function: =iif(isnull([nextid]), " ", null), and set the CanGrow and
CanShrink properties.

This way, you don't have to do any funky formatting in the OnFormat event
handler for the Detail section.