Simply query question

  • Thread starter Thread starter Quack Boy
  • Start date Start date
Q

Quack Boy

I'm new to MS access so here's an easy question:
I've got a simple table:

ID person zipcode Country
1 Fred SO1 2AB UK
2 Betty WA 23568 UK
3 Charlie CA12379 USA

I want to return a query with all the above information but if the
country is "UK" then to not to print the country name at all. ie

ID person zipcode Country
1 Fred SO1 2AB
2 Betty WA 23568
3 Charlie CA12379 USA

I tried it with a iif statement but I just got above info returned but
only if the country wasn't UK - which isn't what I was after.
Naturally I could just search and replace "UK" for nothing and not
bother with the query but I don't want to do that.

Thank you.
 
SELEDT [ID], [Person], [ZipCode],
IIF([Country] = "UK", "", [Country]) AS CountryWOUK
FROM [YourTable]
 
SELEDT [ID], [Person], [ZipCode],
IIF([Country] = "UK", "", [Country]) AS CountryWOUK
FROM [YourTable]
Click to expand...


That didn't seem to work - it kicked out an error about parenthesis
But I did notice the "select" typo.
 
check your original IIf statement... i do stuff like this
all the time.

Make a column in your query

CNTRY: IIf([Country]="UK", Null, [Country])


~notdave
 
check your original IIf statement... i do stuff like this
all the time.

Make a column in your query

CNTRY: IIf([Country]="UK", Null, [Country])
Click to expand...

That's what I had originally but it just returns:

3 Charlie CA12379 USA

So I must be doing something wrong somewhere.
 
Can you post the SQL for the query?
Click to expand...


SELECT Table1.person, Table1.zipcode, Table1.Country
FROM Table1
WHERE (((Table1.Country)=IIf([Country]="UK","",[Country])));
 
the where clause is going to restrict the results, which
(if I understand correctly) you don't want, you just want
to hide a value if that value equals UK

select table1.person, table1.zipcode, IIf([County]
="UK",Null,[Country]) As cnty
FROM table1;



~notdave


-----Original Message-----
Can you post the SQL for the query?
Click to expand...


SELECT Table1.person, Table1.zipcode, Table1.Country
FROM Table1
WHERE (((Table1.Country)=IIf([Country]="UK","", [Country])));


.
Click to expand...
 
Please compare your *whole* SQL and the whole SQL String I posted (ignoring
the wrong spelling for SELECT). My IIf is in the SELECT Clause, NOT the
WHERE Clause (i.e. criteria)!
 
Back
Top