Find ranges or series of integers

  • Thread starter Thread starter coxrail
  • Start date Start date
C

coxrail

I have an integer field named "section". I need a routine that will query
that [section] in a manner like the way Microsoft Word accepts page numbers
for printing. I envision having a search box on a form and entering either
single values (like "18"), multiple values separated by commas (like
"18,23,25") or ranges of values (like "15-18"). Does anyone have any VB code
like this?
 
coxrail said:
I have an integer field named "section". I need a routine that will query
that [section] in a manner like the way Microsoft Word accepts page
numbers
for printing. I envision having a search box on a form and entering either
single values (like "18"), multiple values separated by commas (like
"18,23,25") or ranges of values (like "15-18"). Does anyone have any VB
code
like this?
Click to expand...


I'm not sure I fully understand what you have in mind. The field in the
table is named "section"? Is it that you want to run a query which uses
this field as a criterion, and want the user to be able to specify the
[section] values to be included in various ways, as you describe? How will
that query be used? As recordsource for a report? How will the user's
specification of the section(s) to be included be made? In a text box on a
form?
 
If you are trying to do this as a parameter query, where you run the query,
an input box pops up, and you enter data as you have perscribed, then I think
you are out of luck.

If you have a form, where you enter these values in a textbox, then you
could write some code to parse the input values and build a WHERE clause for
your SQL statement.

Although the following function doesn't contain any error checking, it would
parse a text string and generate an appropriate string that could be appended
to a dynamic SQL statement.

In the click event of a command button, I would have code similar to:

Private Sub cmd_RunQuery_Click

Dim strSQL as string

strSQL = "SELECT * FROM yourTableName " _
& "WHERE [Section] " + ParseNumbers(me.txt_SectionsToQuery)
currentdb.Querydefs("YourQueryName").sql = strSQL

docmd.openquery "YourQueryName"

End Sub

Then put this code either in the forms code module or in a regular code
module. It accepts a string and then creates an array by splitting the
string at the commas. Within each array element, it checks to see whether it
has a hyphen. If not, it appends the array element to the varIn variable; if
so, it splits the array element and loops through the numbers from the start
number to the end number, appending each of those values to varIn. Lastly,
it checks to see whether varIn is NULL, a single value, or a list of values,
and returns an appropriate string to be concatenated to the WHERE clause.

Public Function ParseNumbers(Optional SectionValues As String = "") As Variant

Dim strPages As String
Dim strPageArray() As String, strRange() As String
Dim intLoop As Integer, intLoop2 As Integer
Dim varIn As Variant

varIn = Null
strPageArray = Split(SectionValues, ",")
For intLoop = LBound(strPageArray) To UBound(strPageArray)
If InStr(strPageArray(intLoop), "-") = 0 Then
varIn = (varIn + ",") & strPageArray(intLoop)
Else
strRange = Split(strPageArray(intLoop), "-")
For intLoop2 = Val(strRange(0)) To Val(strRange(1))
varIn = (varIn + ",") & intLoop2
Next
End If
Next

If IsNull(varIn) Then
ParseNumbers = Null
ElseIf Len(varIn) - Len(Replace(varIn, ",", "")) = 0 Then
ParseNumbers = " = " & varIn
Else
ParseNumbers = "IN (" & varIn & ")"
End If

End Function

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Dale Fye said:
If you are trying to do this as a parameter query, where you run the
query,
an input box pops up, and you enter data as you have perscribed, then I
think
you are out of luck.
Click to expand...


No, it could be done, but it would be very inefficient. One would write a
function along the lines of this very quick and dirty one:

'----- start of code -----
Function ValueIsInList( _
InputValue As Variant, _
ListSpec As String) _
As Boolean

Static strList As String
Static alngValueList() As Long
Dim astrItems() As String
Dim astrRange() As String
Dim I As Long

' New spec, build new list.
If ListSpec <> strList Then
strList = ListSpec
astrItems = Split(strList, ",")
ReDim alngValueList(UBound(astrItems), 1)
For I = 0 To UBound(astrItems)
astrRange = Split(astrItems(I), "-")
alngValueList(I, 0) = CLng(astrRange(0))
If UBound(astrRange) = 0 Then
alngValueList(I, 1) = alngValueList(I, 0)
Else
alngValueList(I, 1) = CLng(astrRange(1))
End If
Next I
End If

' Is the value in the list?
ValueIsInList = False

If Not IsNull(InputValue) Then
For I = 0 To UBound(alngValueList, 1)
If InputValue >= alngValueList(I, 0) _
And InputValue <= alngValueList(I, 1) _
Then
ValueIsInList = True
Exit Function
End If
Next I
End If

End Function
'----- end of code -----

Then one could call it in a parameter query like this:

SELECT * FROM MyTable
WHERE ValueIsInList(MyTable.Section, [Enter section(s) wanted:])

As I said, it would be very inefficient, but it would work.
 
Dirk,

Great use of static variables. I stand corrected, but would still encourage
him to do it by identifying the items he wants on a form, and building the
SQL string dynamically.

Actually, I should have mentioned in the original post that I hate it when
applications ask me for a value in a inputbox, or even a textbox, when they
assume I will know what the values mean. Personally, I prefer either a
multi-select list or a subform (that contains check boxes) designed to look
like a multi-select list.

It took me a while to figure out why you were not doing the testing inside
the initial If - Then, but when I finally realized that strList was static,
and you were avoiding rebuilding the array, it made a lot of sense.

--
Dale

email address is invalid
Please reply to newsgroup only.



Dirk Goldgar said:
Dale Fye said:
If you are trying to do this as a parameter query, where you run the
query,
an input box pops up, and you enter data as you have perscribed, then I
think
you are out of luck.
Click to expand...


No, it could be done, but it would be very inefficient. One would write a
function along the lines of this very quick and dirty one:

'----- start of code -----
Function ValueIsInList( _
InputValue As Variant, _
ListSpec As String) _
As Boolean

Static strList As String
Static alngValueList() As Long
Dim astrItems() As String
Dim astrRange() As String
Dim I As Long

' New spec, build new list.
If ListSpec <> strList Then
strList = ListSpec
astrItems = Split(strList, ",")
ReDim alngValueList(UBound(astrItems), 1)
For I = 0 To UBound(astrItems)
astrRange = Split(astrItems(I), "-")
alngValueList(I, 0) = CLng(astrRange(0))
If UBound(astrRange) = 0 Then
alngValueList(I, 1) = alngValueList(I, 0)
Else
alngValueList(I, 1) = CLng(astrRange(1))
End If
Next I
End If

' Is the value in the list?
ValueIsInList = False

If Not IsNull(InputValue) Then
For I = 0 To UBound(alngValueList, 1)
If InputValue >= alngValueList(I, 0) _
And InputValue <= alngValueList(I, 1) _
Then
ValueIsInList = True
Exit Function
End If
Next I
End If

End Function
'----- end of code -----

Then one could call it in a parameter query like this:

SELECT * FROM MyTable
WHERE ValueIsInList(MyTable.Section, [Enter section(s) wanted:])

As I said, it would be very inefficient, but it would work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Dale Fye said:
Great use of static variables. I stand corrected, but would still
encourage
him to do it by identifying the items he wants on a form, and building the
SQL string dynamically.
Click to expand...


Oh, I agree completely -- building the SQL dynamically from information
entered or selected on a form is much better (so long as other requirements
permit it). I was just answering the technical challenge.
 
Excellent. It works exactly the way I needed.

Dirk Goldgar said:
coxrail said:
I have an integer field named "section". I need a routine that will query
that [section] in a manner like the way Microsoft Word accepts page
numbers
for printing. I envision having a search box on a form and entering either
single values (like "18"), multiple values separated by commas (like
"18,23,25") or ranges of values (like "15-18"). Does anyone have any VB
code
like this?
Click to expand...


I'm not sure I fully understand what you have in mind. The field in the
table is named "section"? Is it that you want to run a query which uses
this field as a criterion, and want the user to be able to specify the
[section] values to be included in various ways, as you describe? How will
that query be used? As recordsource for a report? How will the user's
specification of the section(s) to be included be made? In a text box on a
form?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top