435,493 Members | 3,090 Online
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
4 Replies

 P: n/a 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