Expand Range of Numbers with SQL in Access

[nabusafe]

Hello.

So, I am a logistics engineer and I am trying to help my pricing manager build a pricing application tool that will help eliminate her time spent filling in huge excel files with information about pricing bids. I have successfully build an Access form that fills in the areas she wanted filled in but I come across a new problem now:

Every once in a while she will receive an RFP (Request for Proposal) which has a cluster of zipcodes. For example:

Origin City Dest St Destination Zip Range
Bloomington AZ 850-865
Bloomington CA-Central 929-948, 950-953, 956-958

Now to make her bids, she has to manually create rows for each of the numbers in the range. Say for the 850-865 range, she has to make rows for 850, 851, 852, ... 865.

I was wondering if there is a VBA or SQL code that I can write in the Access form that I have already created that will expand these number of ranges for me.

SIDE NOTE: For that second range of zip codes (929-948, 950-953, 956-958) how would you compile the code so that it expands all the ranges after the comma?

If you can help me with this you'd be an absolute life saver!!

The name of my table with this information is tblTemplate.

Thank you all!!

[NeoPa]

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 ','.

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]

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]

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:

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:

<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

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]

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).

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]

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.

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

I thought that was explained in my earlier post :

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 :

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

[nabusafe]

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]

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).

1. Open Access.
2. Open up a database within Access that you're happy to run som testing in.
3. 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).
4. Navigate to the Project Explorer pane using Ctrl-R.
5. Right-click on the name of the project at the top and select Insert & Module.
6. You should see a new module has been added to the list with a name like "Module" plus an integer.
7. If your Access has been correctly configured (See Require Variable Declaration.) you should see in the new module some basic code like :
   

   Option Compare Database
   Option Explicit

   If that isn't there then copy and paste it in anyway and fix your Access setup.
8. 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.
9. Now navigate to the Immediate pane by typing Ctrl-G.
10. Copy and paste my test (?AllNums("123-127,300,401-405");) across into there and hit enter.
11. 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.