Show nothing, depending on the value

[RexesOperator]

I know the title is confusing - but it is only fitting since I am confused.

I have a query for mailing addresses. What I want is to have a null value if [COUNTRY] = "Canada". That is I only want to see the country if it is NOT Canada. This is not the same as <> "Canada" or Not Like "Canada".

As an example the output would be:

Toronto, Ontario
London, England
Sydney, New South Wales, Australia (my apologies to Ozzies for the original mispelling)

I suppose the simplest solution is not to have a value in [COUNTRY] for Canada at all, but I'd like to know how to do this in the query for other fields that I am using.

 

[Bilbo_Baggins_Esq]

As you're building your city/state/country, (e.g. [city] & [state]), when you get to where you want the country use:

iif([country]="Canada","",[Country])

 

[Bilbo_Baggins_Esq]

Or, if the city/state/country are already in one field, you could use a string function to check if the value contains "Canada" and if so, use only the nonCanada portion (assuming canada is always at the end).

iif(instr(1,[City_State_Country],"Canada")>0,left([City_State_Country],Len([City_State_Country])-(instr(1,[City_State_Country],"Canada")+1)),[City_State_Country])

 

[RexesOperator]

I tried that already, but the query comes back with only the countries that aren't Canada. I need to see the records with Canada in them - I just don't want to see the country name Canada in the record's field.

 

[RexesOperator]

Or, if the city/state/country are already in one field, you could use a string function to check if the value contains "Canada" and if so, use only the nonCanada portion (assuming canada is always at the end).

iif(instr(1,[City_State_Country],"Canada")>0,left([City_State_Country],Len([City_State_Country])-(instr(1,[City_State_Country],"Canada")+1)),[City_State_Country])

City/ProvinceState/Country are in separate fields.

 

[Bilbo_Baggins_Esq]

I tried that already, but the query comes back with only the countries that aren't Canada. I need to see the records with Canada in them - I just don't want to see the country name Canada in the record's field.

Don't use this as a criteria, use it as the field itself.

 

[Bilbo_Baggins_Esq]

QueryField:[table].[city] & [table].[ProvinceState] & iif([table].[country]="Canada","", chr(44) & " " & [table].[Country])

With this as the FIELD, and not as the criteria, it should not act as a filter, but rather should build your final query field

 

[RexesOperator]

Lesson learned - I shouldn't try this at 11:30 PM!

iif([country]="Canada","",[Country])

works perfectly in the FIELD.

Thank you for your help.