IIf Query

[ddrumm]

Hi
Im struggling with trying to put the below into a query.

Heres it in English

Non Loss KWH: =IIf([MPRN]='81590699485'
Or [MPRN]='81427150661'
Or [MPRN]='81325023880'
Or [MPRN]='81108542280'
Or [MPRN]='81077371778'
Or [MPRN]='81073326511'
Or [MPRN]='81046871830' equals UOM_Code KWH and RegisterTypeCode = ‘ES’ or ‘IS’ then show Non-Loss

But if [MPRN]=81007282654 equals UOM_Code KWH and RegisterTypeCode = ‘ES’ and not ‘IS’ then show Non-Loss for the ‘ES’ figures

But if [MPRN]=81007282654 equals UOM_Code KWH and RegisterTypeCode = ‘IS’ and not ‘ES’ then show Non-Loss for the ‘IS’ figures

Basicly the above on MPRN 81007282654 is the same but i need seperate answers as one is for Import and the other is for Export

hope this makes sence
Any help would be of great help

 

[KenHigg]

I would suggest putting these in a table to add to the query.

 

[ddrumm]

Hi KenHigg

I already have these in a table, its called tblMPRN

 

[KenHigg]

Then put it in the query and do a relationship between the two tables.

 

[ddrumm]

Ive tried this but it still does'nt work properly,
you see im bringing the Query into a crosstab Query
and i taught if the above would work in 1 field in a query it would give me the answer im looking for

If that makes sence

 

[Brianwarnock]

I don't understand the non loss, do you have 2 non loss fields and if so what do you show for the first baunch of MPRNs if not what do you show as for the difference between IS and ES for the last MPRN.

That apart my instinct is to join the tables MPRN to UOM_Code so that you only get hits then call a function using a simple mixture of Select Case and If but thats when I run into trouble with the Non loss.

Brian