Converted database 97-2003. One query not working anymore. (1 Viewer)

[oam]

Hi guys! I'm new here and pretty new to Access too. However, I have previous experience in database programming from SAP and Lotus Notes environments.

I have an old database made with Access 97 and I converted it to Access 2003 format. It is a database for assigning employees to different tasks each week, and each employee has a set of tasks they can perform. These are maintained in the table EMPLOYEES. The database keeps track of who has performed which tasks at what date.

The problem:
In one of the forms there are tasks listed and an employee selection listbox for each of them. It should list the employee candidates in order of who's the next in turn AND show only those employees who are allowed to perform that specific task (Employee table has a text field "Class" which can have one to three values to reflect this, for example "K,2,3").
The form has a field "Task" which has one character or number to reflect the task in question.
In the form's Employee selection box attributes the Row source Query is like this:

SELECT DISTINCTROW [TAB EMPLOYEES].Employee, 
[TAB EMPLOYEES].LastDate, 
[TAB EMPLOYEES].LastTask
FROM [TAB EMPLOYEES] 
WHERE ((([TAB EMPLOYEES].Class) Like "*|[Task]|*")) 
ORDER BY [TAB EMPLOYEES].LastDate;

I'm getting no entries in the listbox selection at all. I suspect the syntax has changed for the wildcard selection in the WHERE-clause but I can't seems to figure out the correct way to do the "Like"-part.

This works well in the older Access 97 when I tested it. And also, if I remove the Like "*|[Task]|*" part it shows me full list of employees, but obviously not filtered by the task type as I'd like to.

I'll be very grateful for any tips regarding this!

 

[wazz]

hi. welcome to awf.

you have to be careful with []. use it for ranges of values like [1-9], [a-m], etc.
if you're referring to a control on the form, try using the full statement: Forms!frmX!txtTask.
Like "*|" & Forms!etc.

 

[oam]

Thanks for the tip! I use the expression builder to get a full reference to the field I need to evaluate.

However, I'm not able to achieve the wildcard effect correctly. It shows only those entries that have exactly the same value in Class as is the Task. What I need is to show entries that have the current Task anywhere in the field Class (as employee can have many tasks they can perform).

Example:

Employee A has class value of "1,2,4"
Employee B has class value of "1,3"
Employee C has class value of "3,4"

In the form where I assign the tasks I have

1. -> should list only employees A and B.
2. -> should list employee A
3. -> should list employees B and C
4. -> should list employee A and C

How to evaluate the Employee table so that it picks all the employees with the current Task anywhere in the Class field?

I'm not aware what is the meaning of the "|" character in the original query but I remember reading that it's not recommended to be used.

 

[wazz]

Like "*" & Forms!YourForm!Task & "*"

leave out the "|". i thought maybe you needed it.
consider possibly redesigning the table to put the classes/tasks in a many-side table.

 

[oam]

That's it! Using a direct reference to the field and this wildcard syntax it works!

Thank you very much wazz!