A query to show list between two number range.

[Angela]

I have a table with below fields.


START END QUANTITY
7865006070001236700 7865006070001236799 100
7865006070001235500 7865006070001235899 300

Start: 7865006070001236700
End:7865006070001236799
Quantity= End - Start +1 (inabove case it wil be 100)

I want to design a query in such a way that I could generate a list of
these 100 in that query.
Is it possible to do in a query in access?

[Allen Browne]

The normal way to do this would be to create a counting table, with a record
for each number from 0 to at least the max number you need (300 in the
example you gave.) Then create a query that includes the counting table and
your existing one (with no join.) You can then create the expression:
[Start] + [Quantity]
and under that the criteria:
<= [End]

However there are some reasons why this won't work properly for the example
you gave:

a) The numbers are too large. They would need the Decimal data type, and JET
can't handle CDec() correctly, so it probably won't work.

b) Start and End are reserved words, so could give you query errors (such as
"too complex.")
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Post by Angela
I have a table with below fields.
START END QUANTITY
7865006070001236700 7865006070001236799 100
7865006070001235500 7865006070001235899 300
Start: 7865006070001236700
End:7865006070001236799
Quantity= End - Start +1 (inabove case it wil be 100)
I want to design a query in such a way that I could generate a list of
these 100 in that query.
Is it possible to do in a query in access?

[a***@gmail.com]

Post by Angela
I have a table with below fields.
START END QUANTITY
7865006070001236700 7865006070001236799 100
7865006070001235500 7865006070001235899 300
Start: 7865006070001236700
End:7865006070001236799
Quantity= End - Start +1 (inabove case it wil be 100)
I want to design a query in such a way that I could generate a list of
these 100 in that query.
Is it possible to do in a query in access?

Hi Az; am facing the same problem! did you manage to know how to list numbers between range??!!

[John W. Vinson]

Post by a***@gmail.com

Post by Angela
I have a table with below fields.
START END QUANTITY
7865006070001236700 7865006070001236799 100
7865006070001235500 7865006070001235899 300
Start: 7865006070001236700
End:7865006070001236799
Quantity= End - Start +1 (inabove case it wil be 100)
I want to design a query in such a way that I could generate a list of
these 100 in that query.
Is it possible to do in a query in access?

Hi Az; am facing the same problem! did you manage to know how to list numbers between range??!!

Az posted this message nearly four years ago in an already-defunct newsgroup,
no longer supported by Microsoft or regularly monitored by anyone but a few of
us traditionalists. See my .sig for more current forums for Access support.

Just note that the START and END fields in this example are too big for any
sort of Number datatype; they must be Text fields. If you repost your question
in an active forum, please describe the nature of the data in your tables and
a clearer statement of the problem.