Iif statements

  • Thread starter Thread starter Del
  • Start date Start date
D

Del

I have an IIF statement in a Crosstab query field which
uses the Not in ("TEXT","TEXT2",...)Whole statement below,
but this statement is very long as the table is very
large. I tried using a wildcard so I wouldn't have to
type the full names in i.e. ("TE*",...) but this didn't
work. Is it possible to get round this somehow so the
statement can be shortened.

PCT: IIf([RegGP_PCG_Name] Is Null,"unknown",IIf
([RegGP_PCG_Name] Not In ("CRAVEN, HARROGATE AND RURAL
DISTRICT PCT","HAMBLETON & RICHMONDSHIRE","HAMBLETON AND
RICHMONDSHIRE PCT","HARTLEPOOL PCT","LANGBAURGH
PCT","MIDDLESBROUGH PCT","NORTH TEES PCT","SCARBOROUGH,
WHITBY AND RYEDALE PCT","SELBY AND YORK PCT","Darlington
PCT","Durham Dales PCT","Durham And Chester-Le-Street
PCT","Easington PCT","Sedgefield PCT","Derwentside
PCT","Newcastle PCT","Sunderland Teaching
PCT","Northumberland Care Trust"),"OATs",[RegGP_PCG_Name]))

Any help appreciated.
 
Hi,


The principle behind a database is to put data into a table, not in the
code (VBA/SQL/whatever). Having a table, ListOfNames, with one field, f1,
one value per record,


SELECT SWITCH(a.RegGP_PCG_Name IS NULL, 'unknown' ,
z.f1 IS NULL , 'OATs' ,
True, a.RegGP_PCG_Name )


FROM whatever As a LEFT JOIN ListOfNames As z
ON a.RegGP_PCG_Name = z.f1




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top