new query field based on existing field logic

[sfgiantsdude]

I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code. Please let me know if you need any clarifying questions. Thank you!

[pbaldy]

How about:

NewFieldName: IIf(DEPTLOOKUP.Hospital = "Y" AND [Facility Lookup].Hospital = "Y", "Y", "N")

Edit: Go Giants!!

[June7]

These are text fields, not Yes/No Boolean type?

Maybe:

IIf([Facility Lookup].[Hospital] = "Y" And [DeptLookup].[Hospital] = "Y", "Y", "N")

[sfgiantsdude]

this works! thanks so much!