Finding all Numbers in a Range

Roddi

New member
Local time
Today, 07:56
Joined
Jun 26, 2014
Messages
8
Hello everyone,

I have the following problem. I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number. Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.

Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long

DoCmd.Hourglass True

If Not IsNull(Me![txtBox]) Then
'Place the Upper and Lower Range into an Array with a
'Space (" ") as a Delimiter
varRange = Split(Me![txtBox], " ")
'There must be exactly 2 Elements in the Array, they must both be Numbers, and the 1st must be > the 2nd
If UBound(varRange) = 1 And IsNumeric(varRange(0)) And IsNumeric(varRange(1)) And _
(varRange(1) > varRange(0)) Then
lngLow = varRange(0)
lngHigh = varRange(1)
For lngCounter = lngLow To lngHigh
CurrentDb.Execute "INSERT INTO UnitTable ([UnitField]) VALUES (" & lngCounter & ");", dbFailOnError
Next
End If
End If
DoCmd.Hourglass False



I then found a piece of code that is supposed to separate numbers from letters, but I can’t find a way to make it work.



Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function



It would be amazing if someone could give me a few tips on how to solve the problem and how to alter the code to make it work for my specific situation. I have extremely limited experience with coding in Access and mostly use macros. :banghead:

Thank you in advance!
 
is this really the name of your field?
Code:
If Not IsNull(Me![COLOR=red][txtBox])[/COLOR] Then

No, this code looks exactly as I found it online. It has only one text box and I need two. Not sure how to change it.
 
Instead of showing us code that you digged up somewhere, tell us what you want to achieve with some examples.

Welcome to the forum by the way!
 
Further to the questions and comments from others, I'd like to know more about why serial numbers change.

There is no way to have a separate table with all serial numbers because they constantly change.

I would also suggest that before you start coding, or modifying code you found somewhere, you should define exactly WHAT you are trying to do in plain English. Don't get into Access and jargon and code until you know WHAT you are trying to do.
 
ok.. So, I collected some more information about the project, and based on this new information I decided to change my approach. I was able to create a list of all existing serial numbers, for example, from M0001 to M4000 and added additional 3000 numbers for the future. Now I have a list of 7000 serial numbers. Each time someone receives a piece of inventory we enter each item individually. The whole thing with ranges and adding many items at the same time was beyond my Access knowledge.

Thank you all for your responses and sorry for the trouble :)
 

Users who are viewing this thread

Back
Top Bottom