By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,493 Members | 3,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,493 IT Pros & Developers. It's quick & easy.

Indicating skips in sequential numbers on a report

P: n/a
Surely someone has done this before, and I am guessing there is a
simple solution that is eluding me. I have a simple report based on a
recordset. For each record there is a field (RecNum) that is basically
a sequential number from 1 to n. Occasionally there are skips between
numbers. I would like to print 'record skipped' for each record
missing from the sequence. For example, if there were no records
numbered 5 and 6 in a sequence of records numbered 1 to 7 I'd like to
output something like the following:

1...
2...
3...
4...
record skipped
record skipped
7...

Short of creating dummy records, how might I achieve this? Thanks.

Bruce

Jan 18 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Ben
I think the easiest solution is to tweak the recordset for your report.

How about creating a master set of record sequences and then joining
them to your actual report? That way you have real data for a query
based comparison.

e.g.
Master table
Record Seq
1
2
3

Now your real record set
real records
Record Seq Title
1 Record 1
3 Record 2

Now set the Recordset for your report to this query:
SELECT [master table].[Record Seq], [real records].Title
FROM [master table] LEFT JOIN [real records] ON [master table].[Record
Seq] = [real records].[Record Seq];

Ahh, but now you want to display "record skipped" if there is no entry
for that record. So use an if statement to add that text and use this
query as your report recordset:
SELECT [master table].[Record Seq], IIf(IsNull([Title])=True,"record
skipped",[title]) AS [Report Title]
FROM [master table] LEFT JOIN [real records] ON [master table].[Record
Seq] = [real records].[Record Seq];

And there you have it...

Bruce wrote:
Surely someone has done this before, and I am guessing there is a
simple solution that is eluding me. I have a simple report based on a
recordset. For each record there is a field (RecNum) that is basically
a sequential number from 1 to n. Occasionally there are skips between
numbers. I would like to print 'record skipped' for each record
missing from the sequence. For example, if there were no records
numbered 5 and 6 in a sequence of records numbered 1 to 7 I'd like to
output something like the following:

1...
2...
3...
4...
record skipped
record skipped
7...

Short of creating dummy records, how might I achieve this? Thanks.

Bruce
Jan 18 '07 #2

P: n/a
On 18 Jan 2007 09:35:46 -0800, Bruce wrote:
Surely someone has done this before, and I am guessing there is a
simple solution that is eluding me. I have a simple report based on a
recordset. For each record there is a field (RecNum) that is basically
a sequential number from 1 to n. Occasionally there are skips between
numbers. I would like to print 'record skipped' for each record
missing from the sequence. For example, if there were no records
numbered 5 and 6 in a sequence of records numbered 1 to 7 I'd like to
output something like the following:

1...
2...
3...
4...
record skipped
record skipped
7...

Short of creating dummy records, how might I achieve this? Thanks.

Bruce
See Microsoft KnowledgeBase article:
210234 "ACC2000: How to Flag Out-of-Sequence Numbers on a Report"
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Jan 18 '07 #3

P: n/a

Ben wrote:
I think the easiest solution is to tweak the recordset for your report.

How about creating a master set of record sequences and then joining
them to your actual report? That way you have real data for a query
based comparison.

e.g.
Master table
Record Seq
1
2
3

Now your real record set
real records
Record Seq Title
1 Record 1
3 Record 2

Now set the Recordset for your report to this query:
SELECT [master table].[Record Seq], [real records].Title
FROM [master table] LEFT JOIN [real records] ON [master table].[Record
Seq] = [real records].[Record Seq];

Ahh, but now you want to display "record skipped" if there is no entry
for that record. So use an if statement to add that text and use this
query as your report recordset:
SELECT [master table].[Record Seq], IIf(IsNull([Title])=True,"record
skipped",[title]) AS [Report Title]
FROM [master table] LEFT JOIN [real records] ON [master table].[Record
Seq] = [real records].[Record Seq];

And there you have it...
Thank you Ben. I had considered manipulating the recordset itself
(essentially loading it with dummy records as you describe). I had
hoped to find a solution that did not require this and instead would
perhaps utilize the report's detail format and/or print events along
with a counter of some kind.

Bruce

Jan 18 '07 #4

P: n/a

fredg wrote:
On 18 Jan 2007 09:35:46 -0800, Bruce wrote:
Surely someone has done this before, and I am guessing there is a
simple solution that is eluding me. I have a simple report based on a
recordset. For each record there is a field (RecNum) that is basically
a sequential number from 1 to n. Occasionally there are skips between
numbers. I would like to print 'record skipped' for each record
missing from the sequence. For example, if there were no records
numbered 5 and 6 in a sequence of records numbered 1 to 7 I'd like to
output something like the following:

1...
2...
3...
4...
record skipped
record skipped
7...

Short of creating dummy records, how might I achieve this? Thanks.

Bruce

See Microsoft KnowledgeBase article:
210234 "ACC2000: How to Flag Out-of-Sequence Numbers on a Report"
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Thanks Fred. This might be something I can modify to meet my needs.

Bruce

Jan 18 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.