Using String input to select Numbers from Column for Query

[Jekyll]

This is a follow up query to a problem I have been working on for a while now. (Multiple Query in a column I think I called it)<br><br>What I have is a column of numbers representing machines, ranging from 1-999. What I need to be able to do is to select non-sequential samples of these numbers in a query ...<br><br>e.g. 1,8,50,287,700&nbsp;&nbsp;&nbsp;or a shorter sample&nbsp;&nbsp;e.g. 3,400,987<br><br>So the helpful suggestion I got was to use a VB input box, to type in numbers as a string and then to define a module which would search the column for the numbers in that string.<br><br>The simple SQL is as follows;<br><br>SELECT *<br>FROM qryall<br>WHERE Machine IN <br>( funcResponse() );<br><br>The Function was defined in a module as follows;<br><br>Public Function funcResponse() As String<br>&nbsp;&nbsp;&nbsp;&nbsp;funcResponse = InputBox(&quot;Enter Numbers for Query criteria&quot;, &quot;Input Box&quot<br>End Function<br><br><br><br>The Problem is ...<br><br>If i enter a single number into the input box, it will return all the data for that machine as desired. However is i try to enter more than one number it returns a blank table. I have tried separating the numbers by comma etc. to see if that was the problem, but I haven't been able to sort it.<br><br>Any help to finish this off would be great,<br><br>Cheers,<br><br>Jekyll.<br><br>

 

[elizabeth]

It might just be the puctuation. For example if the field is a text datatype you have to surround the value with quote maks. If multiple values it looks like this:<br>in(&quot;a&quot;,&quot;b&quot;,&quot;c&quot.

 

[Jekyll]

Thanks for the suggestion.<br><br>Unfortunately it still won't recognise the individual numbers. Any other ideas?<br><br>Might it be to do with the definition of the function?<br><br>Or is it more likely to do with the punctuation required for a number datatype?<br><br>Cheers,<br><br>Jekyll.

 

[Geekette]

dear jekyll,<br>to select a random set of records try using the <b>top</b> function as in:<br><br><b>select top 5 <i>[fieldname1]...[fieldnamex]</i><br>from <i>[tablename]</i><br>where....</b><br><br>this will return a random set of records <u>unless</u> you add the<br><b>order by [machine_id]</b> or whatever is your numerical index, at which point the &quot;real&quot; top {in numerical order} will be returned. if you use a text field, the result will be in alphabetical order (and not random).<br><br>notes<br>the number after <b>top</b> is whatever number of values you need.<br>you may also use <b>bottom</b>; same syntax