Generating list of numbers from one start # and one end #

[WilsonsW]

Hi all,

I have a fun problem that has got me stuck. I have a data entry database that contains the field "SheetNumber". I also have a master list of all sheet numbers that must be entered. This list looks something like this:

1 - 199
206 - 314
405 - 789
etc.

These sheets will be entered linearly (so sheet 1 will be entered first, then 2, through to 99, then 206, 207, etc)

What I want to do is check to make sure that every time an end number is entered, all other numbers have also already been entered. So this check would make sure that sheet numbers 1-199 are all entered, and it will be prompted by the entering of 199.



I can do this if I have a list that contains each value from 1-199 in a separate record, but i can't figure out how to create this table automatically. There are so many combinations of start and end times that it would be incredibly inefficient to create this list by hand. So my question is: how do I create a table that contains each numerical value between the start # and the end # in a separate record?

[WilsonsW]

Ok All,
I've come pretty close on my own. The below vba should create a temporary table that I can then compare the data to. However, I am getting a parameter error (too few parameters. Expected 2). I don't get it. Any ideas for why I am getting the error?

Me.Start = Me.End.Column(1)
If Me.Start = Me.End.Column(1) Then
strStart = Me.Start
strEnd = Me.End

CurrentDb.Execute ("SELECT [Thousands].[nbr]+[Hundreds].[nbr]+[Tens].[nbr]+[Ones].[nbr]+[" & strEnd & "] AS nbNum INTO ztemp" & vbCrLf & _
"FROM [SELECT (( COUNT(*) - 1 ) * 1000 ) AS nbr " & vbCrLf & _
"FROM ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS A " & vbCrLf & _
"INNER JOIN ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS B " & vbCrLf & _
"ON ( A.id <= B.id ) " & vbCrLf & _
"GROUP BY A.id ]. AS Thousands, [SELECT (( COUNT(*) - 1 ) * 100 ) AS nbr " & vbCrLf & _
"FROM ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS C " & vbCrLf & _
"INNER JOIN ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS D " & vbCrLf & _
"ON ( C.id <= D.id ) " & vbCrLf & _
"GROUP BY C.id ]. AS Hundreds, [SELECT (( COUNT(*) - 1 ) * 10 ) AS nbr " & vbCrLf & _
"FROM ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS E " & vbCrLf & _
"INNER JOIN ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS F " & vbCrLf & _
"ON ( E.id <= F.id ) " & vbCrLf & _
"GROUP BY E.id ]. AS Tens, [SELECT ( COUNT(*) - 1 ) AS nbr " & vbCrLf & _
"FROM ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS G " & vbCrLf & _
"INNER JOIN ( SELECT TOP 10 id FROM MSysObjects ORDER BY id ) AS H " & vbCrLf & _
"ON ( G.id <= H.id ) " & vbCrLf & _
"GROUP BY G.id ]. AS Ones " & vbCrLf & _
"WHERE ((([" & strStart & "])>=[" & strEnd & "]) AND (([Thousands].[nbr]+[Hundreds].[nbr]+[Tens].[nbr]+[Ones].[nbr]) Between 0 And ([" & strEnd & "]-[" & strStart & "]))) " & vbCrLf & _
"ORDER BY Thousands.nbr, Hundreds.nbr, Tens.nbr, Ones.nbr;")

Else
End If