list all the number between two sets given (1 Viewer)

hllary

Registered User.
Local time
Today, 06:10
Joined
Sep 23, 2019
Messages
80
I have an access form where the user enters two set of numbers, for example 63 and 120. On another table I need to have all the numbers >=63 and <=120 listed.

How would I do this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:10
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! Have you tried using a query? For example:
Code:
SELECT * FROM TableName WHERE FieldName >= Forms!FormName.ControlName AND FieldName <= Firms!FormName.ControlName
 

hllary

Registered User.
Local time
Today, 06:10
Joined
Sep 23, 2019
Messages
80
Thank you. Maybe I'm missing something but the output only show the the first and second numbers not all the one in between.

Below is my code:

Code:
SELECT [abc Tracker def].VarianceNumber, [abc Tracker def].[SPH Start], [abc Tracker def].[SPH End] INTO AnotherTable
FROM [abc Tracker def]
WHERE ((([abc Tracker def].[SPH Start])>=[abc Tracker def].[SPH Start]) AND (([abc Tracker def].[SPH End])<=[abc Tracker def].[SPH End]));

I need the query to show all list all the numbers between the users entered numbers. I don't not want a query that only selects a certain numbers. For example, if a user enters 52 and 60 the query will show 52, 53,,,,59,60 for that user.
 
Last edited:

Micron

AWF VIP
Local time
Today, 09:10
Joined
Oct 20, 2018
Messages
3,476
I think the question is how to generate all the whole numbers between any 2 values that are provided. These missing numbers don't actually exist in any table. Correct?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:10
Joined
May 7, 2009
Messages
19,175
you need to reference your Form:
Code:
..
..
WHERE [abc Tracker def].[SPH Start])>=[Forms]![yourFormName]![startDateTextboxName] AND [abc Tracker def].[SPH End])<=[Forms]![yourFormName]![endDateTextboxName]
 

hllary

Registered User.
Local time
Today, 06:10
Joined
Sep 23, 2019
Messages
80
Micron, that is correct. I'm trying to "generate all the whole numbers between any 2 values that are provided. These missing numbers don't actually exist in any table."
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:10
Joined
May 7, 2009
Messages
19,175
so you need to Add those numbers to the table?
or just generate them on the fly?
 

hllary

Registered User.
Local time
Today, 06:10
Joined
Sep 23, 2019
Messages
80
I'll have a button on a form which will run an update query and have them added to another table.
 

Micron

AWF VIP
Local time
Today, 09:10
Joined
Oct 20, 2018
Messages
3,476
IMHO it would be best if you stated why you want to do this as there may be better alternatives. It might even point out that the whole exercise is moot.

There are 2 basic ways I know of to generate such a list. One involves a function, which is probably the best approach. The other is to write a query/sub query. However, that approach requires that you use a table that can provide a number seed. That can be a table you create that just has numbers for this. Or the table must have an autonumber field that will always contain all the numbers you'll ever need. The potential problem with that is that if records get deleted, you have gaps in the seed values - not to mention that there's no guarantee that an AN field is always incremented by 1.

As I said, before offering examples of either method, I think it would be best to know why.
 

hllary

Registered User.
Local time
Today, 06:10
Joined
Sep 23, 2019
Messages
80
I have a table where the user enters a unique number/letter then they give vehicle numbers (in column b they write the smallest number. in column c they write the largest number).
The vehicle numbers go from 1 to 300. So instead of user entering all the vehicles, they write the smallest and largest number. On a separate table I will have all the vehicle's numbers that are assigned to each unique number/letter.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:10
Joined
May 7, 2009
Messages
19,175
last question, are those numbers on the first table? or do you generate them on the fly and save it to 2nd table?
 

hllary

Registered User.
Local time
Today, 06:10
Joined
Sep 23, 2019
Messages
80
The first and last vehicle number given on the first table, but the numbers in between can be on either the first or on a 2nd table.
 

isladogs

MVP / VIP
Local time
Today, 13:10
Joined
Jan 14, 2017
Messages
18,186
I have a table where the user enters a unique number/letter then they give vehicle numbers (in column b they write the smallest number. in column c they write the largest number).
The vehicle numbers go from 1 to 300. So instead of user entering all the vehicles, they write the smallest and largest number. On a separate table I will have all the vehicle's numbers that are assigned to each unique number/letter.

Perhaps I'm being dense but why can't the vehicle numbers be assigned to each unique number/letter in the main table? I don't see the need for the second table.
 

hllary

Registered User.
Local time
Today, 06:10
Joined
Sep 23, 2019
Messages
80
each unique number has more than one vehicle assigned to it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:10
Joined
May 7, 2009
Messages
19,175
ok, based from your last post, the numbers are already in either table?
here goes:

create a query first, name it qryNumbers:
Code:
SELECT T1.NUMS+1+(T2.NUMS*10)+(T3.NUMS*100) AS NewNum FROM
(SELECT DISTINCT Abs([ID] Mod 10) AS NUMS
FROM MSYSOBJECTS) As T1,
(SELECT DISTINCT Abs([ID] Mod 10) AS NUMS
FROM MSYSOBJECTS) As T2,
(SELECT DISTINCT Abs([ID] Mod 10) AS NUMS
FROM MSYSOBJECTS) As T3);

create another query (final query) on [abc Tracker def] table and qryNumbers:
Code:
SELECT [abc Tracker def].VarianceNumber, qryNumbers.NewNum 
    FROM [abc Tracker def], qryNumbers 
    WHERE qryNumbers.NewNum BETWEEN [abc Tracker def].[SPH start] 
             AND [abc Tracker def].[SHP end];
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:10
Joined
Feb 19, 2002
Messages
42,981
Some of us are still confused, mostly because it doesn't generally make sense to just add a bunch of rows to a table with only a sequence number filled in.

We still don't understand the purpose, Rows should only be added to a table when they contain some data that needs to be saved in a separate row. So far, your description doesn't rise to that standard. What other data goes into these rows and when is it entered? and why aren't the rows just entered when the other data is available?

There are reasons for doing stuff like this. For example, when a teacher gives a test, he might generate a record for each student who needs to take the test with null as the actual grade to indicate that the test has not yet been graded. The individual records serve as holding spots for the grades since EVERY student will eventually have a grade and rather than have an audit turn up a "missing" row at some future time, ALL rows are added now and the grade is filled in later. If the rows are not added "now", it will be difficult "later" to determine if a particular student's record is just not scored or if he was never supposed to take the test.
 

Micron

AWF VIP
Local time
Today, 09:10
Joined
Oct 20, 2018
Messages
3,476
Agree with the confusion thing. I was thinking "So if I have to add 10 vehicles I'm supposed to know that the last one entered was 100 thus I enter 101 as a start value and 110 as the end value?" What if I miscalculate? What if 2 or more concurrent users are entering a block of numbers? The method doesn't make sense; at least not as it's being interpreted. There are other ways of entering group data. Which is best depends on who's doing what and maybe at the same time.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:10
Joined
Jan 23, 2006
Messages
15,364
hllary,

By now you must recognize your post is confusing readers. Can you step back from HOW you think you should solve something and tell readers in simple English what you are trying to achieve. There may be options, but before we can give focused advice, we have to understand the basic problem.
You have numbers and forms and at least 2 tables in your proposed approach. But you mention vehicles, so what is the business issue to be resolved?
 

Users who are viewing this thread

Top Bottom