423,682 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Expand Range of Numbers with SQL in Access

P: 4
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!!
Sep 7 '18 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,084
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 ','.
Expand|Select|Wrap|Line Numbers
  1. Public Function AllNums(strNums As String) As String
  2.     Dim lngX As Long
  3.     Dim astrRange() As String
  4.     Dim varString As Variant
  5.  
  6.     For Each varString In strNums
  7.         astrRange = Split(CStr(varString), "-")
  8.         If UBound(astrRange) > 0 Then
  9.             For lngX = CLng(astrRange(0)) To CLng(astrRange(1))
  10.                 AllNums = AllNums & "," & lngX
  11.             Next lngX
  12.         Else
  13.             AllNums = AllNums & "," & astrRange(0)
  14.         End If
  15.     Next varString
  16.     If Len(AllNums) > 0 Then AllNums = Replace(Mid(AllNums, 2), ' ', '')
  17. End Function
Sep 7 '18 #2

P: 4
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!
Sep 7 '18 #3

zmbd
Expert Mod 5K+
P: 5,285
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function AllNums(strNums As String) As String
  2.      Dim lngX As Long
  3.      Dim astrRange() As String
  4.      Dim varString As Variant
  5.  astrRange = Split(CStr(strNums), "-")
  6.      For Each varString In astrRange
  7.          If UBound(astrRange) > 0 Then
  8.              For lngX = CLng(astrRange(0)) To CLng(astrRange(1))
  9.                  AllNums = AllNums & "," & lngX
  10.              Next lngX
  11.          Else
  12.              AllNums = AllNums & "," & astrRange(0)
  13.          End If
  14.      Next varString
  15.      If Len(AllNums) > 0 Then
  16.       AllNums = Replace(Mid(AllNums, 2), " ", "")
  17.     End If
  18.  End Function
Proof of concept usage in the Immediate pane:
Expand|Select|Wrap|Line Numbers
  1. <ctrl><g>
  2. ?AllNums("929-948")
  3. 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
Expand|Select|Wrap|Line Numbers
  1. SELECT ([ones]![pk])+([tens]![pk]*10)+([Hundreds]![pk]*100) AS Beans
  2. 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...
Sep 7 '18 #4

NeoPa
Expert Mod 15k+
P: 31,084
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).
Expand|Select|Wrap|Line Numbers
  1. Public Function AllNums(strNums As String) As String
  2.     Dim lngX As Long
  3.     Dim astrRange() As String
  4.     Dim varString As Variant
  5.  
  6.     For Each varString In Split(strNums, ",")
  7.         astrRange = Split(CStr(varString), "-")
  8.         If UBound(astrRange) > 0 Then
  9.             For lngX = CLng(astrRange(0)) To CLng(astrRange(1))
  10.                 AllNums = AllNums & "," & lngX
  11.             Next lngX
  12.         Else
  13.             AllNums = AllNums & "," & astrRange(0)
  14.         End If
  15.     Next varString
  16.     If Len(AllNums) > 0 Then AllNums = Replace(Mid(AllNums, 2), " ", "")
  17. End Function
Sep 9 '18 #5

NeoPa
Expert Mod 15k+
P: 31,084
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 :
Expand|Select|Wrap|Line Numbers
  1. ?AllNums("123-127,300,401-405");
  2. 123,124,125,126,127,300,401,402,403,404,405
Sep 9 '18 #6

P: 4
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
Sep 10 '18 #7

NeoPa
Expert Mod 15k+
P: 31,084
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 :
    Expand|Select|Wrap|Line Numbers
    1. Option Compare Database
    2. 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.
Sep 10 '18 #8

Post your reply

Sign in to post your reply or Sign up for a free account.