-----Original Message-----
You have a table with fields to record a minimum and maximum value:
StartValue Number
EndValue Number
and you want a query to return a record for every value in between?
If that's the idea, you need another table that contains counter values: one
for each possible number.
Create a query that has both tables. If you see any line joining the 2
tables, delete the line. It is the lack of any join (known as a Cartesian
product) that gives you a record for each value.
Now, in the Criteria row under the counter field from the counter table,
enter:
Between [Table1].[StartValue] And [Table1].[EndValue]
This results in every value from the counter table generating a row if it
falls between the 2 values (both inclusive).
To programmatically populate the counter table with all values between 1 and
100,000 you could use something like this:
Function MakeData()
'Purpose: Create the records for a counter table.
Dim db As DAO.Database 'Current database.
Dim rs As DAO.Recordset 'Table to append to.
Dim lng As Long 'Loop controller.
Const conMaxRecords As Long = 100000 'Number of records you want.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblCount", dbOpenDynaset, dbAppendOnly)
With rs
For lng = 1 To conMaxRecords
.AddNew
!CountID = lng
.Update
Next
End With
rs.Close
Set rs = Nothing
Set db = Nothing
MakeData = "Records created."
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
I am writing a query just now where one of the fields I
want to give it a start and end figure how do I complete
this , also if I want this to be able to do a query on
various different fields in the database but only search
on the parameter that I input while the rest of them may
be null is this possible
.
