|
|
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" <coxrail[ at ]discussions.microsoft.com> wrote in message
news:E8D73879-1997-4DE1-8F55-6DAD1E1901CC[ at ]microsoft.com...
[Quoted Text] >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?
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)
|
|
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.
"coxrail" wrote:
[Quoted Text] > 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?
|
|
"Dale Fye" <dale.fye[ at ]nospam.com> wrote in message
news:A5DF245B-CA6B-4B83-8BCD-B349ADD81E0B[ at ]microsoft.com...
[Quoted Text] > 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.
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)
|
|
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" wrote:
[Quoted Text] > "Dale Fye" <dale.fye[ at ]nospam.com> wrote in message
> news:A5DF245B-CA6B-4B83-8BCD-B349ADD81E0B[ at ]microsoft.com...
> > 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.
>
>
> 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)
>
>
|
|
"Dale Fye" <dale.fye[ at ]nospam.com> wrote in message
news:4B9613CB-FDAC-4FD7-B90C-8398B8CC8C7F[ at ]microsoft.com...
[Quoted Text] >
> 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.
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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
Excellent. It works exactly the way I needed.
"Dirk Goldgar" wrote:
[Quoted Text] > "coxrail" <coxrail[ at ]discussions.microsoft.com> wrote in message
> news:E8D73879-1997-4DE1-8F55-6DAD1E1901CC[ at ]microsoft.com...
> >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?
>
>
> 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)
>
>
|
|
|