Expand Range of Numbers with SQL in Access

**NeoPa** · Sep 7 '18, 05:27 PM

This question covers two distinct topics, so let's forget writing out the new records to a table for now. You can post that later once you're happy with the technique of producing the individual numbers in an array or string. FYI we only allow one topic per thread as they become close to useless otherwise, for those searching for solutions to their problems.

Let's start then, with a function procedure that returns a string of all the numbers from a string that can include multiple ranges of numbers where a range is indicated by a '-' and items are separated by a ','.

Code:

Public Function AllNums(strNums As String) As String
    Dim lngX As Long
    Dim astrRange() As String
    Dim varString As Variant

    For Each varString In strNums
        astrRange = Split(CStr(varString), "-")
        If UBound(astrRange) > 0 Then
            For lngX = CLng(astrRange(0)) To CLng(astrRange(1))
                AllNums = AllNums & "," & lngX
            Next lngX
        Else
            AllNums = AllNums & "," & astrRange(0)
        End If
    Next varString
    If Len(AllNums) > 0 Then AllNums = Replace(Mid(AllNums, 2), ' ', '')
End Function

**nabusafe** · Sep 7 '18, 06:15 PM

Thank you so much for your response!
However I am a bit confused, where am I entering this code and what exactly will it do for me? Sorry for the confusion!

**zmbd** · Sep 7 '18, 08:18 PM

nabusafe:
Neopa's code is supposed to take the string input
Finds the numbers
Splits them out
Find the lowest and highest
Creates the values between these two
Returns a comma delimited string.
AllNums("929-948")
929,930,931,... 948

NeoPa,
Compile error:
Line6: strNums is not a collection object nor an array
Line16: From personal experience with the Replace() Compiler will not like the single quotes in this context- IDK why - it just doesn't like them in this syntax.

Modified code:

Code:

Public Function AllNums(strNums As String) As String
     Dim lngX As Long
     Dim astrRange() As String
     Dim varString As Variant
 astrRange = Split(CStr(strNums), "-")
     For Each varString In astrRange
         If UBound(astrRange) > 0 Then
             For lngX = CLng(astrRange(0)) To CLng(astrRange(1))
                 AllNums = AllNums & "," & lngX
             Next lngX
         Else
             AllNums = AllNums & "," & astrRange(0)
         End If
     Next varString
     If Len(AllNums) > 0 Then
      AllNums = Replace(Mid(AllNums, 2), " ", "")
    End If
 End Function

Proof of concept usage in the Immediate pane:

Code:

<ctrl><g>
?AllNums("929-948")
929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948

I also have the compact number expansion query...
Table: Multipliers, single field [PK] as numeric(long) record entries 0 to 9
then create the following query

Code:

SELECT ([ones]![pk])+([tens]![pk]*10)+([Hundreds]![pk]*100) AS Beans
FROM Multipliers AS ones, Multipliers AS tens, Multipliers AS Hundreds;

One could use this in several ways to achieve the same effect using parameters, vba on the fly recordset, etc...

**NeoPa** · Sep 9 '18, 07:45 PM

Hi Z.

Sorry about that, and thanks for catching them. Brain fart time (s two). I should really have checked it before submitting. I admit it was air-code.

I did a last-minute reshuffle of the code to replace line #6 and got it wrong. I also know exactly why single quotes don't work in VBA. It's because VBA doesn't support them. Nothing to do with the Replace() function at all. The parameters that are supposed to be passed to it are unintelligible to VBA as VBA uses double-quotes (") exclusively. The SQL standard uses single-quotes (') exclusively but Jet & ACE both allow double-quotes (") in their SQL too. It's supposed to make it easier for those learning, but just causes confusion (TBF it does make it marginally easier for very new users but then continues to confuse everyone but the rare few that understand why they did it that way).

Code:

Public Function AllNums(strNums As String) As String
    Dim lngX As Long
    Dim astrRange() As String
    Dim varString As Variant

    For Each varString In Split(strNums, ",")
        astrRange = Split(CStr(varString), "-")
        If UBound(astrRange) > 0 Then
            For lngX = CLng(astrRange(0)) To CLng(astrRange(1))
                AllNums = AllNums & "," & lngX
            Next lngX
        Else
            AllNums = AllNums & "," & astrRange(0)
        End If
    Next varString
    If Len(AllNums) > 0 Then AllNums = Replace(Mid(AllNums, 2), " ", "")
End Function

**NeoPa** · Sep 9 '18, 07:52 PM

Originally posted by Nabusafe

Nabusafe:
However I am a bit confused, where am I entering this code ...

You need to insert it into a Standard Module then you can call it from the Immediate Pane (See Debugging in VBA.) to test it.

Originally posted by Nabusafe

Nabusafe:
... and what exactly will it do for me?

I thought that was explained in my earlier post :

Originally posted by NeoPa

NeoPa:
... the technique of producing the individual numbers in an array or string.

That is a list of all the numbers that are covered by the string format you explained you use (List separator = "," & Range separator = "-").
I tried it with an example list and the results were as below :

Code:

?AllNums("123-127,300,401-405");
123,124,125,126,127,300,401,402,403,404,405

**nabusafe** · Sep 10 '18, 03:36 PM

Thank you guys both for your help. I really appreciate it.

I will be honest I'm not very proficient with VBA so all of this code is kind of confusing.

Is there anyway someone could attach a downloadable file that shows how all of this code is ran and executed? I would truly be grateful

**NeoPa** · Sep 10 '18, 04:52 PM

Originally posted by Nabusafe

Nabusafe:
Is there anyway someone could attach a downloadable file that shows how all of this code is ran and executed? I would truly be grateful.

I won't go that far, I think that's too much of an ask, but I'll try to explain easily in simple steps. It will be easier to understand if you've read the previously linked article (Debugging in VBA).

Open Access.
Open up a database within Access that you're happy to run som testing in.
Press Alt-F11 (Hold Alt key while quickly pressing F11 once, then release Alt key.) to open up the VBIDE (Visual Basic Integrated Development Environment).
Navigate to the Project Explorer pane using Ctrl-R.
Right-click on the name of the project at the top and select Insert & Module.
You should see a new module has been added to the list with a name like "Module" plus an integer.
If your Access has been correctly configured (See Require Variable Declaration.) you should see in the new module some basic code like :
Code:
```
Option Compare Database
Option Explicit
```
If that isn't there then copy and paste it in anyway and fix your Access setup.
After these two lines paste the code found in post #5. If you need to then remove enough spaces from the start of all lines so that the first and last lines are fully to the left within the module.
Now navigate to the Immediate pane by typing Ctrl-G.
Copy and paste my test (?AllNums("123-127,300,401-405");) across into there and hit enter.
Feel free to try other data passed across to the function and see what comes back out.

There's no need to save this module in that particular database. If you close the database it should prompt you to save that module or not.

Expand Range of Numbers with SQL in Access

Expand Range of Numbers with SQL in Access

Comment

Comment

Comment

Comment

Comment

Comment

Comment