searching for only part of a field

[laurat]

Let me explain my situation and hopefully it makes sense. I have a form with a field on it called Heat Num. I have used an input mask to allow the user to add up to 5 separate numbers in this field. Now I would like to create a query that allows the user to type in a six digit number and have it search through all records, even the records where the Heat Num field may have several numbers in that one field. For example, I would like it to be able to find the Heat Num 123456 in a Heat Num field that has 125555, 123456, 789789. I have heard of using the Like operator but don't know quite how to use it or if it would be the correct thing to use in this case. All suggestions are welcome and appreciated!!

Thank you,
Laura

 

[pdx_man]

How does 123456 relate to 789789? The number of characters? Sequencing?

 

[laurat]

I was just using that as an example. The field would contain several numbers 123456 being one of them.

The field is called Heat Num and could contain one 6 digit number up to five 6 digit numbers, each separated by a comma for example:

123456, 987654, 111111, 222222, 333333

and I want to run a query that will prompt the user for the Heat Number they are looking for, if they type 123456 I want the results to include the above example even though there are other numbers listed in the field.

I hope that is a little clearer.

Laura

 

[pdx_man]

Ah, got it.

For the Criteria on Heat Num use:

Like "*" & [Enter Heat Number] & "*"

 

[Pat Hartman]

You will be much better off in the long run if you structure your tables properly. What are you going to do if the user decides he needs 6 heat numbers? Once you have more than 1 heat, you effectively have many. Heat should be stored in a separate table that is related to the main table by the main table's primary key. Do some reading on database design and specifically 1-to-many relationships.