Conditional field display problem

[IamreallyStuck]

I am new to Access, so this may be easy to do, but it has got me stuck!

I have two fields in a table:
'FilmTitle' and 'Responsibility'
The 'Responsibility' field contains details about Film directors, producers, actors, etc.

What I need is that if the 'Responsibility' field for a record contains either the word 'directed' or 'produced', for the field to be blank in my query, but to show up if these words are not present.

I've used the following criteria in the 'Responsibility' field:

Not Like "directed" And Not Like "produced"

but this method means that the entire record does not display, whereas I still want the record to display, just not the 'Responsibility' field.

Can anyone help me out with this? : )

Lee.

 

[nealvenables]

Hi,

Try this query...

SELECT FilmTitle,
iif((Responsibility like "*produced*" or Responsibility like "*direct*"), "", Responsibility)
FROM YourTable

 

[IamreallyStuck]

Thanks for the tip Neal,
I've tried this, but when I try to run the query I get the following error message that I don't understand:

'You have written a subquery that can return more than one field without using the Exists reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.'

Does this make any sense to you?

Thanks,
Lee.

 

[nealvenables]

Surprised that there's a subquery involved here(!). Can you post the SQL statement that your query is based on and I'll take a peek...

 

[IamreallyStuck]

I've managed to figure out why it wasn't working - I was typing the SQL into the criteria box instead of into the SQL view! So, everything is fine now : ) Thanks very much for your advice,
Lee.