Hi all. New to the forum. I have a query with many fields. 2 of the fields are REGION and COUNTRY. REGION is limited to the values A, B, C, D, E. Country can be any country within those regions. I want both fields included in my query results. However, If Region = B then I want the COUNTRY value to be blank.
I would love to be able to set it up using simple query criteria. I tried entering this formula into the COUNTRY field criteria iif([REGION]<>"B",[COUNTRY],"") and iif([REGION]<>"B",[COUNTRY],NULL). Both of them deleted all the records for REGION B from the results, which I don't want.
I also tried to create 2 separate queries, one for A,C,D,E and one for B in which B did not include the field COUNTRY and then join them with a UNION query, which failed due to inconsistent fields.
My last option, that I can think of, is to use a make table query and then use an update query to set the value of COUNTRY = "" if REGION = B. I am reluctant to do this because of the manual nature of it and worry it could get forgotten during an update (I create and use many macros in Excel, but have never attempted one in Access).
Any help would be appreciated.
Thanks