Invalid Argument Error On A Select Query

[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---]

 

[Wayne Morgan]

The help file says that a string variable is 10 bytes + the string length
with a range of 0 to about 2 billion. I don't see any limitations on the
Left$ and other string functions that would reduce this, but that doesn't
mean they don't exist. By your description, I assume it works with any
number of selections less than or equal to the limit (18) that you've run
into. I also looked up the In() clause to see if it had a limit, but again
the help file didn't specify one, although I'm sure there is one.

Have you tried placing some Debug.Print statements in the code to have the
string results print to the Immediate window? This will let you see what the
functions are returning.

If the problem does turn out to be a limit on the length of a string, there
is another way to do this than by using an In() clause. You could assign the
values to a table that you use just for this. Add that table to the query
and link it to the current table on the associated field. This will also
limit that field to only those records that match the records in the new
table. You would clear the table and add the selections to it each time
before you open the query.

--
Wayne Morgan
MS Access MVP

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---]

 

[Guest]

Thanks Wayne. That's about all I could find about limitations on a String
too. I have checked the Debug.Print statements in a few places to confirm
that all is looking well and haven't seen anything that would raise a flag.
I think I will take a shot at converting this to going through a table
instead of building up a String and then processing off of that.

matt

The help file says that a string variable is 10 bytes + the string length
with a range of 0 to about 2 billion. I don't see any limitations on the
Left$ and other string functions that would reduce this, but that doesn't
mean they don't exist. By your description, I assume it works with any
number of selections less than or equal to the limit (18) that you've run
into. I also looked up the In() clause to see if it had a limit, but again
the help file didn't specify one, although I'm sure there is one.

Have you tried placing some Debug.Print statements in the code to have the
string results print to the Immediate window? This will let you see what the
functions are returning.

If the problem does turn out to be a limit on the length of a string, there
is another way to do this than by using an In() clause. You could assign the
values to a table that you use just for this. Add that table to the query
and link it to the current table on the associated field. This will also
limit that field to only those records that match the records in the new
table. You would clear the table and add the selections to it each time
before you open the query.

--
Wayne Morgan
MS Access MVP

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---]

 

[Guest]

I managed to get this problem resolved while still using a string to build up
the SQL instead of dumping to a table and adding it to the query. It seems
that the problem lies in that module for the inparam function. I really have
no idea why it failed when trying to pass the string before (maybe something
to do with a 127/128 character limit for a string that is passed to a
function... although that seems quite not right). But I changed a few bits
of code around to manually build the SQL instead of passing that part off to
a function. So I changed
sWhere =
"((inparam([tblFloorManager].[FloorManagerUID],[Forms]![frmStatsQuery].[sSelections]))=True)"
to
sWhere = "([tblFloorManager].[FloorManagerUID] IN (" & sSelections & "))"
and all works perfectly now. And it was a very simple fix. So I thought
I'd let you know and get this posted up here for anyone else that may be
running into this problem.

The help file says that a string variable is 10 bytes + the string length
with a range of 0 to about 2 billion. I don't see any limitations on the
Left$ and other string functions that would reduce this, but that doesn't
mean they don't exist. By your description, I assume it works with any
number of selections less than or equal to the limit (18) that you've run
into. I also looked up the In() clause to see if it had a limit, but again
the help file didn't specify one, although I'm sure there is one.

Have you tried placing some Debug.Print statements in the code to have the
string results print to the Immediate window? This will let you see what the
functions are returning.

If the problem does turn out to be a limit on the length of a string, there
is another way to do this than by using an In() clause. You could assign the
values to a table that you use just for this. Add that table to the query
and link it to the current table on the associated field. This will also
limit that field to only those records that match the records in the new
table. You would clear the table and add the selections to it each time
before you open the query.

--
Wayne Morgan
MS Access MVP

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---]