Can I tell a Query to only show the data of a field if it is different.... (1 Viewer)

[CarysW]

I'm creating mailing labels, I have customers all over the world and I will be using the same query to create all labels. I need the query to only give me the country name if it is not England, Scotland or Wales. Will Access let me specify this? If so, what do I tell it to do?

Is it something I need to specify in the report or VBA rather than the query?

 

[Atomic Shrimp]

Sure can. In the query design grid, in the criteria row, beneath the country name column just type:
Not Like "England" and not like "Scotland" and not like "Wales"

Alternatively, I think this probably also works:
Not in("England", "Scotland", "Wales")

Obviously if one of the values in that column has been mis-typed ("Sctoland", for example), it won't be omitted from the results.

 

[CarysW]

Sure can. In the query design grid, in the criteria row, beneath the country name column just type:
Not Like "England" and not like "Scotland" and not like "Wales"

Alternatively, I think this probably also works:
Not in("England", "Scotland", "Wales")

Obviously if one of the values in that column has been mis-typed ("Sctoland", for example), it won't be omitted from the results.

But would this not omit all of the entry rather than just the Country name?

I'll explain; when I print out address labels I don't need it to put 'England', 'Scotland' or 'Wales' at the end but I do need it to have 'Ireland', 'USA', 'France' etc. But the country information for all countries is needed in the Master Table for other queries so I can't take it away.

 

[Atomic Shrimp]

Ah - I misunderstood your question.

Yes, it's possible to do this using the Immediate If function - assuming your country column is called 'country', all you need to do is, instead of the field name at the top of the grid, type:

Expr1: Iif([country] in("England", "Scotland", "Wales"),"", [country])

(you can replace 'Expr1' with something a bit more friendly if you want)

 

[CarysW]

Ah - I misunderstood your question.

Yes, it's possible to do this using the Immediate If function - assuming your country column is called 'country', all you need to do is, instead of the field name at the top of the grid, type:

Expr1: Iif([country] in("England", "Scotland", "Wales"),"", [country])

(you can replace 'Expr1' with something a bit more friendly if you want)

Wonderful! Thankyou!