search for Random numbers

[ghost]

Greeting,
I have a form that has a text box for search, what I want to do is once I
input a random numbers in that text box example: 1, 4, 6, 8 9, a query open
and display all record which refereed on that text box.
How and I do that?

 

[Tom Wickerath]

Hi Ghost,

What do your numbers represent? Hopefully it is not a numeric key
(autonumber or long integer) values, because these are generally considered
meaningless--users should never need to search for records in this manner.

I suppose you could always use VBA code to build the WHERE portion of a SQL
statement on-the-fly, using the IN keyword. Something like this:

SELECT * FROM MyTable WHERE FieldName IN (1, 4, 6, 8, 9);

This, of course, assumes a numeric data type for the FieldName in question.
If it is a text data type, then you'd need to create the WHERE clause
on-the-fly like this:

SELECT * FROM MyTable WHERE FieldName IN ("1", "4", "6", "8", "9");

Such code would, among other things, need to strip off the trailing comma
that you indicated. Also, it might want to insert a comma, as appropriate,
where the user forgot to use one. Your original example is missing a comma
between the 8 and the 9.

Before pursuing this idea further, tell us more about what you are
searching. There may be a different approach that you have not considered.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[ghost]

Thank you Tom, you always No.1
What I need to do is if a user wants to search for random numbers 1, 6, 9, 4
or whatever user like not as we know in range from 1 to 9 , but the problem
is how to perform that in text box and what is the suitable code to be typed
in query or VB

 

[Tom Wickerath]

If I understand you correctly (?), you want the capability for a user to find
completely random records. Is this correct? If so, are they to find a random
number of records each time, or some set number of random records (such as 5
records given in your opening example).

I'm thinking that perhaps you can use some of the logic from this KB article:

(ACC2000 How to Fill a Table with Random Records from Another Table
http://support.microsoft.com/?id=210616

so that the records retrieved are truly random, instead of the user
influencing which records by entering numbers such as 1, 4, 6, 8, and 9.
Note: I added the parentheses around the "ACC2000:" part in the title,
because this KB article should be easy enough to apply to other versions of
Access as well. Using the logic in this article, the user could specify the
number of random records, and then click a button. Or, you could provide a
drop down combo box, based either on a lookup table or a hard-coded value
list, which allowed the user to select how many records, with your list
containing appropriate values (for example, a minimum of 3 random records,
and maybe 5, 7 and a maximum of 9 random records).

Rather that add the random records to a table within the same database, I
think I would use the idea of a temporary linked table to help prevent
unnecessary bloat of the database. Here is a link that shows a sample of
doing this:

http://www.accessmvp.com/TWickerath/downloads/tmpwrkdb.zip

Then, simply base a subform that uses this temporary linked table as it's
recordsource. Any new searches will require that you first delete records
from this temporary linked table, but that's easy to do in code with
something like this:

CurrentDB.execute "DELETE * FROM MyLinkedTableName", dbFailOnError

(Or, use a variable set = CurrentDB, if you need to use CurrentDB elsewhere
in the same procedure).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________