Conditional query 1

[MA04]

Hi all,

I want a query that first takes into consideration Null values in field WCD (this is a code) but displays parts of the code due to each circumstance. The condition i want is:

if is null then display WCD (show all records even if null),
else if the first 2 characters of WCD = 95 then display from the left the first 3 characters,
else if first 2 characters are integers but not 95 then left 4 characters,
else if first character not integer then left 2 characters.

e.g. if code = 956c2 it will result = 956
if code = 359BC it will result = 359B
if code = BC92 it will result = BC

The query at present looks like this:

SELECT I1.POSTCODE, I1.PRMF, I1.STRD, I1.WCD
FROM I1
WHERE (((I1.POSTCODE)=[enter a postcode:]));

Any help very much appreciated, thanks in advance,
M-.

 

[PHV]

You may try something like this:
SELECT POSTCODE, PRMF, STRD,
IIf(WCD Like '95*',Left(WCD,3),
IIf(WCD Like '[0-9][0-9]*',Left(WCD,4),
Left(Nz(WCD,""),2))) As myWCD
FROM I1
WHERE POSTCODE=[enter a postcode:];


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Thats great PHV, thanks again for your help,

M-.