Generate series of numbers

[DaveRobichaud]

Hey everybody!

I'm trying to build a small application that will basically generate a
range of numbers for a specified beginning and end point.

However, it also needs to "shorten" the numbers in assuming that if I
wanted a range from 2000 to 2059, the result should be 200, 201, 202,
203, 204, and 205.
Understanding that the results mean 200*, 201*, and so on.

Conversly, if I put in 2000 to 2038, it will give me results like this:
200, 201, 202, 2030, 2031, 2032, 2033, 2034, 2035, 2036, 2037, 2038.

Also, the to and from numbers are not limited to 4 digits... they can
be as high 7 or 8 digits long.

I really don't even know where to begin with this.
Any help would be greatly appreciated!
Thanks in advance,
Dave

 

[RobFMS]

Dave

This is an interesting puzzle to solve. What I'm not understanding is the
rule(s) for shortening a number.

Conversly, if I put in 2000 to 2038, it will give me results like this:
200, 201, 202, 2030, 2031, 2032, 2033, 2034, 2035, 2036, 2037, 2038.

If I specify a range from 2000 to 2038, what rule(s) allow me to display
200, 201, 202, etc. ??

If you give us that much information, I think we can put together something
for you.

Rob Mastrostefano


--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/

 

[Douglas J Steele]

He did explain, Rob, although I'm curious as to the why, since BETWEEN would
seem to be more appropriate (and wildcards don't work with numbers)

200 is because he wants all numbers that start with 200 (i.e. 200*), 201
because he wants all numbers that start with 201 (201*) and 202 because he
wants all numbers that start with 202 (202*). 2030 through 2038 each have to
be spelled out in full, because he doesn't want all of the numbers starting
with 203 (i.e.: he can't use 203*, because that will also match 2039, which
isn't in the desired range)

Of course, he mentioned that the numbers could be 7 or 8 digits. If he takes
looks at 200*, he's going to get values like 20093, 200064 and 20000356,
which certainly aren't in the range 2000 to 2059.

 

[John Vinson]

Hey everybody!

I'm trying to build a small application that will basically generate a
range of numbers for a specified beginning and end point.

Answered in microsoft.public.access.

Please - DO NOT multipost. If you wish to ask a question in more than
one newsgroup (two or three at the very most) please crosspost instead
- put the newsgroup names on the Newsgroups line. This lets the
volunteers see that an answer has already been posted, rather than
wasting time giving you an answer that you already have.

John W. Vinson[MVP]

 

[RobFMS]

Douglas

Thanks for the clarification. It didn't stand out at the time of my posting
as to what was going on.

Sounds like there is going to be a lot of toggling between strings and
numbers conversions to get this to work.

Rob Mastrostefano

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/

 

[RobFMS]

Douglas

Let me know if you agree with some of this thoughts...

1. Create a form that allows
... the user to enter the starting and ending number.
... the user to enter the target values separated by a comma (or some
delimiter)

2. Create a table
... column 1 = NumberValue (Text 10)
... column 2 = KeepMe (Yes/No), Default=False

3. Given the user's starting and ending values, push those numbers into the
table.
Yes, this can be very time consuming and/or lengthy. Sometimes you just
may not
have much choice.

4. Use the Split() function to pull apart the target values

5. Loop through each target value.
... user a query that sets KeepMe to True if the value wildcard matches
a record in
the table.

The end result should give you all values that meet the target criteria.
That is, all records where the column KeepMe = True.

Rob Mastrostefano




--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/

 

[DaveRobichaud]

Hey guys,
Thanks for the feedback. This definitely puts me on the right track.

Douglas:
You were bang-on with the clarification! (I'll try to put more details
next time... )

Also, what I meant by the arbritrary number of digits is there will
always be the same number of digits in the to and from
specifications...
so, I could ask for a range of 2000 to 2050
but I could also ask for a range from 4000000 to 4500000

One thing to note is that I will require the user to indicate two
numbers that have the same number of characters.

Rob:
Thanks for the suggestion... although, I don't think I quite follow the
4th and 5th step. Could you maybe expand on your thoughts?


In case anybody is curious, the reason I need this, is because I need
to key-enter surcharges for zip codes around the world. I work for a
shipping company and every year, the zip codes that are affected by
surcharges change.
When I receive the final list of zip codes, they come in ranges... and
it's a very long and tedious task to key all of these ranges manually.
The system in which I key the zip codes can accept wild cards, so it
makes the process a lot easier (and shorter)... if only I can figure
out a quick way to generate it.

Furthermore, zip codes around the world range from 4 to 7 characters
long, hence, the reason to account for longer numbers.

Regards,
Dave


PS: Sorry for the double-posting. I originally posted in
microsoft.public.access.forms and realized that this question may not
necessarily be "forms" related, so I deleted it and re-posted in
microsoft.public.access. I must not have deleted in time, because
obviously, there are tons of answers now. So, again, my apologies.