Help with complex 'IF' function(s) ...

[Captain Database ...!!]

Even thought I AM Captain Database ... (!!) Fighter of the ~EviL~ data error ... (!!) ... ehemm, I am not toooo familiar with Access 'IF' functions. How would I write the following in Access (copied from another freebie datasoft')?



Test1:
IF ([GR_GT_collees] <> -99 AND [GR_GT_abimees] <> -99) THEN [GR_GT_collees] + [GR_GT_abimees] must be 0< and <300 ELSE [GR_Saines] + [GR_Parasitees] + [GT_Vides] must be 0< and <300

Test2:
IF ([GR_GT_collees] <> -99 AND [GR_GT_abimees] <> -99) THEN [GR_GT_collees] + [GR_GT_abimees] + [GR_PT] must be 0< and <400 ELSE [GR_Saines] + [GR_Parasitees] + [GT_Vides] + [GR_PT] must be 0< and <400

Test3:
IF ([GR_GT_collees] <> -99) THEN [GR_Gaines] + [GR_Vides] + [GR_Parasitees] must equal [GR_GT_collees]

Test4:
IF ([Tree] <> -99 AND [Cone] <> -99) THEN [Operation] x 10,000,000 + [Stand] x 10,000 + [Tree] x 100 + [Cone] must equal a distinct integer

[khalid]

Did you try to use the IIF() function?

[Captain Database ...!!]

I am trying to use the IIF function, but am a little unsure of how to write it. Can all of these conditions be put into one? How would I write test 1, for instance? This is my attempt so far, although it is rejected:

IIF([GR_GT_collees]=-99 AND [GR_GT_abimees]=-99, ([GR_GT_collees]+[GR_GT_abimees]) 0< AND <300 ELSE ([GR_Saines]+[GR_Parasitees]+[GT_Vides]) 0< and <300

[Captain Database ...!!]

I've actually managed to achieve the results I wanted by splitting the specific "if" condition, and the required/associated ranges, between two separate field-criteria. 'Can' such a complex function be entered into 'one' field, however, or is breaking it down into several, simpler fields the way to go? e.g., test1 as split between two separate fields/criteria;

=field1= tests_IIf(i): IIf([GR_GT_collees] And [GR_GT_abimees]<>-99,1,0)
=criteria= 1

=field2= ([GR_Saines]+[GR_Parasitees]+[GR_Vides])
=criteria = >0 and <300

[boblarson]

This would be the correct usage (If I understand your request):


IIF([GR_GT_collees] <> -99 AND [GR_GT_abimees] <> -99, IIf(Nz([GR_GT_collees],0)+Nz([GR_GT_abimees],0) > 0 AND Nz([GR_GT_collees],0)+Nz([GR_GT_abimees],0) <300, Nz([GR_GT_collees],0)+Nz([GR_GT_abimees],0), 0))