Adding Records between two ranges 2

[serino]

Hello Everyone!

I am not sure if this is possible but I have to two fields which have a datatype of ShortText. The Fields are called StartNumber and EndNumber. The startnumber will always start with the letter "A" as in this example "A1971400" and the end number "A1971500". Is it possible to somehow create a query that will insert the range that is in between the start and end number and append this to a table?

Thank you!

 

[Andrzejek]

You may create a table with a field that contains:[tt]
ID
A0000000
A0000001
A0000002
...
A9999998
A9999999[/tt]
and you can always do:[tt]
Select ID From ThisTable
Where ID Between "A1971400" and "A1971500"[/tt]

or

Sub InsertRecords()
Dim strStart As String
Dim strEnd As String
Dim i As Long

strStart = "A1971400"
strEnd = "A1971405" [green]'"A1971500"[/green]

For i = CLng(Mid(strStart, 2)) To CLng(Mid(strEnd, 2))
    Debug.Print "Insert record with A" & i
Next i

End Sub

and you get:[tt]
Insert record with A1971400
Insert record with A1971401
Insert record with A1971402
Insert record with A1971403
Insert record with A1971404
Insert record with A1971405
[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[serino]

That's an idea! I will try this to see if this will work for what I need. Thank You and Happy Holidays!

 

[Andrzejek]

When you get it done, post your solution here for the benefit of others


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Since this forum is about SQL, why not an SQL solution... like

SELECT MAX(b.ID)
FROM YourTable a, AndyTable b
WHERE b.ID > a.StartNumber AND b.ID < a.EndNumber

You could use MAX(b.ID) or MIN(b.ID) which ever you like, since there will be one or more values in the argument for the MIN or MAX.

Since StartNumber and EndNumber are separate fields, I'd assume that StartNumber < EndNumber in ALL cases. Otherwise you'd need a bunch of error trapping.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[serino]

Thanks Skip! I going to go at this with a different approach.

I have striped of the letter there by just creating a numerical value. If I have table1 that contains "startnumber" and "Endnumber" and lets say the startnumber is 1401 and the endnumber is 1501. How do I create a query that will place all the numbers sequentially into a new table2 based off the startNumber and EndNumber?

1401
1402
1403... until I reach 1501

 

[SkipVought]

Sorry, my mistake. I didn't read your original post completely.

SELECT b.ID
FROM YourTable a, AndyTable b
WHERE b.ID > a.StartNumber AND b.ID < a.EndNumber

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Here are a few ideas of how to do it in a query, but I would still create a 'helper table' with the values from 0 to 99999 or whatever you need. I believe that's what Skip uses as AndyTable in his posts.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Another link: Generate a set or sequence without loops

Where the author states:

Numbers table
I've long been an advocate of storing an auxiliary "numbers table" on disk (and, for that matter, a calendar table as well).

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[serino]

Skip,

What is the Andy Table?

 

[SkipVought]

You may create a table with a field that contains:
ID
A0000000
A0000001
A0000002
...
A9999998
A9999999

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

And since serino said:

I have striped of the letter there by just creating a numerical value.

this table would look like:[pre]
ID
0
1
2
...
9999998
9999999[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[serino]

Got it! I understand now how it will work. Thank you both so much for the help and happy holidays!