Search question containing Like statement

[MCAdude]

I have a query where I want the user to get its output by giving a number, the problem is the field can contain more than 1 number, but the current query returns only the fields with the given number.

Example: The user types in 16 and he get's all the fields with only 16
What I want is a return of all fields with " number number 16 number"

The number is variable so I can't lock it in advance.

Thanks for the help

 

[Pat Hartman]

You can use the Like operator in the query:

Select ...
From ...
Where YourField Like "*" & [Enter Value] & "*";

The problem with this of course is that when you look for *16*, you will also get 116 and 161 or any other string that includes 16.

The real problem is the table design. It is NEVER appropriate to store multiple values in a single field. Your table contains a one-to-many relationship and this requires two tables to be properly handled.

 

[MCAdude]

Thanks for the help, and I know the structure of the table isn't ideal but the numbers are in fact a concatenation of strings (it's quite difficult to explain)


[This message has been edited by MCAdude (edited 03-04-2002).]

 

[Pat Hartman]

In a perfect world all tables would be in fifth normal form. Most of us are willing to settle for third. But not even being in first normal form is really problematic. It doesn't matter that the field is a concatenation of strings. It is still wrong to glomp multiple things into a single field. You will have much less trouble if you store data properly. It is very likely that used on a field like this, the Like * syntax will select incorrect data.