Using Like In A Query

[matethreat]

Hello,

I am trying to filter a subform that is based on an item selected in a combo box.

What I have is a Combo box that lists job numbers (with a text format) and a subform that lists all the people who worked on that job.

My problem is that in the Job Number field of the subform an employee may have worked on several jobs in a day, and all those numbers are entered into one text string.

For example:

John Doe Worked On 040511/041276/041388

If I create a query on it's own I can put the following criteria in it and John Doe appears in the list.

crtieria: like "*040511*"

I would like to do something similar with the filtering of my subform, but because I am using my combo box as the source of the job number I can't seem to get the same result.

I have used the following criteria:
Like [Forms]![Scheduling]![Combo8]

Which will only match employees who have worked on the one job in a day. I have tried several methods but I cannot get the result.

Does anyone have any ideas.

Thank You

Destiny Is Not The Chances We Take, But The Descisions We Make.

 

[PHV]

Something like this ?
" Like '*" & [Forms]![Scheduling]![Combo8] & "*'"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[matethreat]

PH,

With
" Like '*" & [Forms]![Scheduling]![Combo8] & "*'"
I Get No Result

If I Modify It To Look Like This
Like "*" & [Forms]![Scheduling]![Combo8] & "*"
I Get Only Exact Matches

This is a tricky one.

Destiny Is Not The Chances We Take, But The Descisions We Make.

 

[jjlogan]

Considerations for you:
1. If I understand your table design, you are putting multiple job numbers in one field using a "/" between them. If that is so, I recommend creating a separate table that contains only a few fields to show employee, job number, and date. In this table, you would have three records for a single employee who worked on three jobs in one day. Then use this table (linked to other tables that give info about the employee and about the jobs.)
2. If you do have the multiple jobs in single field and don't create a new table, you may be able to some VBA coding to parse out job numbers by employee by date and put this information in Array variable in preparation for input to a form - but I haven't worked specifically with that process recently. See what others say.
Jeff

 

[matethreat]

Thank you for your responses.

I was able to get it to work by creating a seperate query and then using that query as the source for my subform. For some reason it works this way, instead of having my subform with it's own internal query.

Aaron

Destiny Is Not The Chances We Take, But The Descisions We Make.