G
Guest
Hello,
[Working with Access2000]
I have been working on getting a SELECT query built up from input off of a
form and have managed to get it working as needed except for one part. The
problem seems to be coming from a listBox I have containing a list of
employees (with multiselect set to extended). All works well until I attempt
to run the query with 19 employees selected; 18 works, 19 doesn't. The query
actually runs off of the employee's ID number, which is a six-digit number,
and I process the selected employees with an inparam(...) function [see below
for code]. With the commas included, 18 employees gives me a 125-character
string, while 19 selected employees gives a 132-character string. So this
has me thinking that there is a 127-character limit on something buried in
the code, but I have not been able to track down anything like that. I
really can't think of why else I would be receiving this error. Can anyone
think of what might be happening here? For now, I will just paste the code
for the inparam(...) functionality below, and if need be, I can post the code
I have for the cmdSearch_Click() sub that does a lot of the work. Let me
know if any more clarification or details is needed. Thanks.
matt
[---Begin Code---]
Option Compare Database
Option Explicit
' these functions were taken from Microsoft Knowledge Base Article 210530
' Richard
'============================================================
' The GetToken() function defines the delimiter character.
'============================================================
Public Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function
'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the In() operator.
'============================================================
Public Function InParam(Fld, Param)
Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function
[---End Code---]
[Working with Access2000]
I have been working on getting a SELECT query built up from input off of a
form and have managed to get it working as needed except for one part. The
problem seems to be coming from a listBox I have containing a list of
employees (with multiselect set to extended). All works well until I attempt
to run the query with 19 employees selected; 18 works, 19 doesn't. The query
actually runs off of the employee's ID number, which is a six-digit number,
and I process the selected employees with an inparam(...) function [see below
for code]. With the commas included, 18 employees gives me a 125-character
string, while 19 selected employees gives a 132-character string. So this
has me thinking that there is a 127-character limit on something buried in
the code, but I have not been able to track down anything like that. I
really can't think of why else I would be receiving this error. Can anyone
think of what might be happening here? For now, I will just paste the code
for the inparam(...) functionality below, and if need be, I can post the code
I have for the cmdSearch_Click() sub that does a lot of the work. Let me
know if any more clarification or details is needed. Thanks.
matt
[---Begin Code---]
Option Compare Database
Option Explicit
' these functions were taken from Microsoft Knowledge Base Article 210530
' Richard
'============================================================
' The GetToken() function defines the delimiter character.
'============================================================
Public Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function
'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the In() operator.
'============================================================
Public Function InParam(Fld, Param)
Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function
[---End Code---]