Only enter a certain number of values in a text box? Access 2000

[Joe]

I am writing a program that will allow you to type multiple codes in a
text box and display the records associated with those numbers on a
form. However, I am only able to enter 18 seperate numbers, after that
it won't display any records. Here's the query that's tying the text
box to the form:

SELECT [Master].[WH], [Master].[Item No], [Master].[DESC1], [Master].
[DESC2], [Master].[UM], [Master].[Vendor No]
FROM Master
WHERE (([Master].[WH])=[forms]![dashboard]![WHFrame]) And ((" " &
[forms]![dashboard]![number] & " ") Like "*[ ]" & [Master].[Item No] &
"[ ]*");

It works exactly like it should as long as I enter 18 numbers or less,
but at 19 it just diplays an empty form. Any ideas?

 

[Jeff Boyce]

Joe

Coming up with a work-around to process/parse multiple facts in one
field/control means extra work for both you and Access. Have you looked
into using the tools Access offers for relationally designed data?

For example (and to make it much easier on your application's users), what
about the idea of using paired listboxes (see the query wizard for an
example in action). The 'left' listbox could hold valid codes (and ONLY
valid codes), while the 'right' listbox would hold those selected as search
terms. Your dynamic SQL statement could then parse the list in the 'right'
box.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

 

[Joe]

Joe

Coming up with a work-around to process/parse multiple facts in one
field/control means extra work for both you and Access.  Have you looked
into using the tools Access offers for relationally designed data?

For example (and to make it much easier on your application's users), what
about the idea of using paired listboxes (see the query wizard for an
example in action).  The 'left' listbox could hold valid codes (and ONLY
valid codes), while the 'right' listbox would hold those selected as search
terms.  Your dynamic SQL statement could then parse the list in the 'right'
box.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Thank you for the response/suggestion. The reason I ended up doing it
this way is because there are 54,000 unique 6-digit codes. Because
everyone is familiar with how to find these codes, and are use to
typing them over and over, I figured the text box option would be ok.
I am just not sure what is limiting me to only entering 18 codes and
still getting a result.

I am still refreshing my knowledge of Access, it's been about 7 years
since I've worked with it, so if there is any other suggestions,
please feel free to mention them.

 

[Joe]

I've no idea why you are limited to 18 values, but you'll find a couple of
methods of handling value list parameters at:

http://support.microsoft.com/kb/100131/en-us

The second method, using the InParam and GetToken functions is more reliable
in that it will handle values which are a substring of another value, which
would lead to a false result with the first method using the Instr function
unless this possibility was specifically catered for.  Using the second
method your query would be:

SELECT [WH], [Item No], [DESC1], [DESC2], [UM], [Vendor No]
FROM Master
WHERE [WH]=[forms]![dashboard]![WHFrame] AND
INPARAM([Item No], [forms]![dashboard]![number]);

One thing to note when using the InParam function is that values of string
data type would not need to be wrapped in quotes when entering the value list
in the control, as is normally the case if the IN operator is used against a
literal value list, just delimited by commas, e.g.

Apple,Pear,Banana

Ken Sheridan
Stafford, England

Thank you Ken. I tried the InParam function before I settled for the
code I used, but I must have messed up the implementation. It now
works.

Unfortunately, I still can only enter 18 codes.

 

[Joe]

Weird!  I've no idea why that should be the case I'm afraid.  What happens if
you test the value list in a query using the IN operator:

SELECT [WH], [Item No], [DESC1], [DESC2], [UM], [Vendor No]
FROM Master
WHERE [WH]=[forms]![dashboard]![WHFrame] AND
[Item No] IN (123456,234567,345678 <and so on>);

Can you enter more than 18 then and get a valid result set?

Ken Sheridan
Stafford, England

If I use the IN operator, I can enter more than 18.

 

[Joe]

Well, I have been trying to implement your solution Ken, with no
success. I have found that when I delimited the numbers in the text
box with a comma, I can input 18 before it will return blank results.
However, if I delimit them with a comma and a space, I can input only
16 before it returns blank results. Am I reaching some limit to
passing arguments with a text box? I have changed query designs and
condensed some of the modules with no success. Any ideas?