Ilf statement error

[sha7jpm]

hi,

have been scratching my head about this and cannot get it to work
I have some schools and when they submit their targets I need to show those schools that have not submitted their targets etc,

the ilf statement below works perfectly except for where the field is empty, I have tried Is null / =0 / <1 but it does not show up the right message (KS2 alert Targets Submitted) so it is only the last ilf statement that is falling over..
any pointers?

many thanks
John

*************************
Expr1: IIf([Key Stage Indicator]="KS2 School Only" And [KS2Nor]>0 And [Eng]>0 And [Mat1]>0,"Targets Submitted",IIf([Key Stage Indicator]="KS1 School Only" And [KS1Nor]>0 And [Read]>0 And [Writ]>0 And [Mat]>0,"Targets Submitted",IIf([Key Stage Indicator]="KS1 & KS2 School" And [KS2Nor]>0 And [Eng]>0 And [Mat1]>0,"Targets Submitted",IIf([Key Stage Indicator]="KS1 & KS2 School" And [KS1Nor] Is Null And [Read] Is Null And [Writ] Is Null And [Mat] Is Null And [KS2Nor]>0 And [Eng]>0 And [Mat1]>0,"KS2 alert Targets Submitted","Targets Not Received"))))

 

[Mile-O]

Expr1: IIf([Key Stage Indicator]="KS2 School Only" And [KS2Nor]>0 And [Eng]>0 And [Mat1]>0,"Targets Submitted",IIf([Key Stage Indicator]="KS1 School Only" And [KS1Nor]>0 And [Read]>0 And [Writ]>0 And [Mat]>0,"Targets Submitted",IIf([Key Stage Indicator]="KS1 & KS2 School" And [KS2Nor]>0 And [Eng]>0 And [Mat1]>0,"Targets Submitted",IIf([Key Stage Indicator]="KS1 & KS2 School" And [KS1Nor] Is Null And [Read] Is Null And [Writ] Is Null And [Mat] Is Null And [KS2Nor]>0 And [Eng]>0 And [Mat1]>0,"KS2 alert Targets Submitted","Targets Not Received"))))

Urgh!

Firstly, (ENG and MAT, KS1NOR and KS2NOR) - alarm bells; repeating groups.

Personally, I'd write a VBA function and let that do it for me otherwise all that's going to be a slow query the more records it becomes based upon.

ie..

NewField: GetValue([Key Stage Indicator], [Eng], [Mat], [KS1Nor], [KS2Nor])

And in the function build the IF THEN ELSE structure that you need to return a value. You can comment the code too so that should you need to change any of the logic you'll know exactly what does what - overlong calculations don't afford you that luxury.

 

[sha7jpm]

quite agree!
many thanks for the input,
as for the field names, you are completely right,

I wanted them changed, but this is a dbase which is not ours and cannot be changed by mere mortals in such establishments, perish the thought!

luckily the table is small, only 100 records, and will not increase so the query will not enlarge too much, but yes I would opt for the vbcode idea.

ta

John