IsNull/Not IsNull usage in Iif Query headache

[Eqrepro]

Hello:

I am a moderately capable Access user, however I am self-taught, so there are some holes in my knowledge which have a tendency to be filled when I run into a problem. Like now...

I have a table in which I want to present in a report the lowest number of three fields. These fields are entered or calculated within the table and/or query, or may be empty of data. They are as follows:

RawStrawConc
PreFreezeConc
PostThawConc

In what is probably an inelegant manner, I perform the sort to produce a final value (called "UseCount") by breaking the items into different calculations:

PreFreezeCount: IIf(Not IsNull([RawStrawConc])<Not IsNull([PreFreezeConc]),[RawStrawConc],[PreFreezeConc])

PostThawCount: IIf([PostThawConc] Is Null,1,[PostThawConc])

UseCount: IIf(([PostThawCount]>1<[PreFreezeCount]),[PreFreezeCount],[PostThawConc])

As can be seen, the first calculation is intended to present the lowest of RawStrawConc or PreFreezeConc, if neither is an empty value. This creates a value called "PreFreezeCount" which is then used in the last calculation.

The second calculation is intended to create another value for use in the last calculation "PostThawCount" and will give an empty (Null) field in "PostThawConc" a value of 1.

The last calculation is intended to compare PostThawCount and PreFreezeCount and return the lowest value, while excluding a PostThawConc which has no entry (and has therefore been given a value of 1).

The annoying and puzzling thing is that it seems to work some of the time, so I am at a loss as to what it is which is messing up.

This is what is returned in one query and I have highlighted the aberrations - which seem to have no regularity... Perhaps there is a more elegant and effective way of achieving this? I am sure there is and hope someone might be able to assist me with it!!

(Apparently I cannot display links or images until I have been present on the board longer - not unreasonable! - so I don't know if my uploaded image will be saved or not, but if it is, it was loaded at "access-programmers.co.uk/forums/attachment.php?attachmentid=59580" with the http and www prefixes of course).

 

[jdraw]

For clarity:

You have 3 fields
RawStrawConc
PreFreezeConc
PostThawConc

Any or all could be Null

What value do you assign if a field is NULL?
What exactly are (plain English --not a formula/expression)

PreFreezeCount
PostThawCount
UseCount ??

 

[plog]

([PostThawCount]>1<[PreFreezeCount])

This never evaluates to false in Access. I thought it would throw a syntax error, when it didn't I threw every possible permutation at it and it always returned True.

I'd rewrite that evaluation using AND and/or OR, whichever the case needs to be.

 

[Eqrepro]

For clarity:

You have 3 fields
RawStrawConc
PreFreezeConc
PostThawConc

Any or all could be Null

Correct.

What value do you assign if a field is NULL?

Err... other than the "1" applied in the second formula, none.

What exactly are (plain English --not a formula/expression)

PreFreezeCount
PostThawCount
UseCount ??

These are numbers derived from other entries made in the table (although I'm not sure if that answers what you were asking).

 

[jdraw]

Consider:

RawStrawConc =400
PreFreezeConc is NULL
PostThawConc =20

What are the calculations and final values for:

PreFreezeCount
PostThawCount
UseCount??

 

[Eqrepro]

Consider:

RawStrawConc =400
PreFreezeConc is NULL
PostThawConc =20

What are the calculations and final values for:

PreFreezeCount
PostThawCount
UseCount??

PreFreezeCount: 400
PostThawCount: 20
UseCount: 400 :banghead:

 

[jdraw]

So those are the final values.
What about

What are the calculations and final values for

It was the description/quasi expression/format of the calculation that wasn't/isn't clear.

 

[Eqrepro]

So those are the final values.
What about


It was the description/quasi expression/format of the calculation that wasn't/isn't clear.

OK, I'm sorry - I'm not understanding you. In my initial post I posted the formulae:

PreFreezeCount: IIf(Not IsNull([RawStrawConc])<Not IsNull([PreFreezeConc]),[RawStrawConc],[PreFreezeConc])

PostThawCount: IIf([PostThawConc] Is Null,1,[PostThawConc])

UseCount: IIf(([PostThawCount]>1<[PreFreezeCount]),[PreFreezeCount],[PostThawConc])

So I'm not quite sure what you're asking. Sorry...

 

[shoji]

Hi Eqrepro,

You can appreciate Access's powerful Nz function. (Google it.)

For example, if A is null, Nz(A,0) will return 0, otherwise A.

My solution for your question (probably):

PreFreezeCount = IIF(Nz(RawStrawConc, 0) < Nz(PreFreezeConc, 0), _
                               Nz(RawStrawConc, 0), _
                               Nz(PreFreezeConc, 0))

UseCount = IIF(PreFreezeCount < Nz(PostThawConc, 0), _
                      PreFreezeCount, _
                      Nz(PostThawConc, 0))

You can do all this in one line:

UseCount = 
IIF(IIF(Nz(RawStrawConc, 0) < Nz(PreFreezeConc, 0), Nz(RawStrawConc, 0), Nz(PreFreezeConc, 0)) < Nz(PostThawConc, 0), IIF(Nz(RawStrawConc, 0) < Nz(PreFreezeConc, 0), Nz(RawStrawConc, 0), Nz(PreFreezeConc, 0)), Nz(PostThawConc, 0))

Elegant? No.

Shoji

 

[Eqrepro]

UseCount =
IIF(IIF(Nz(RawStrawConc, 0) < Nz(PreFreezeConc, 0), Nz(RawStrawConc, 0), Nz(PreFreezeConc, 0)) < Nz(PostThawConc, 0), IIF(Nz(RawStrawConc, 0) < Nz(PreFreezeConc, 0), Nz(RawStrawConc, 0), Nz(PreFreezeConc, 0)), Nz(PostThawConc, 0))

Shoji - the problem with this appears to be that it returns a zero value as being the lowest - although strangely, it returned zero values even in calculations where the three fields had a value...

 

[shoji]

Hi Eqrepro,

Most likely I didn't understand your question clearly. I took "the lowest number of three fields" as a cue. Yes, if any of them are null, the returned value is 0.

Do you mean that if any field is null, exclude it before calculation? What if all of them are null?

Anyway, if that is what you want, this is the code. However, if all of them are null, it returns 0. I wrote this way for the sake of readability, but you should remove all the spaces when you test it. MsgBox will show the results.

PreFreezeCount = IIF(Nz(RawStrawConc)="", 
                              IIF(Nz(PreFreezeConc)="", 0, PreFreezeConc
                                  ),  
                              IIF(Nz(PreFreezeConc)="", RawStrawConc,
                                   IIF(RawStrawConc < PreFeezeConc,
                                        RawStrawConc, 
                                        PreFreezeConc
                                        )
                                  )
                             )

MsgBox PreFreezeCount


UseCount = IIF(IIF(Nz(PostThawConc)="",
                           PreFreezeCount,
                           IIF(PostThawConc<PreFreezeCount,
                                PostThawCont,
                                PreFreezeCount
                               )
                         )
                     )

MsgBox UseCount

Shoji

 

[Eqrepro]

Thanks Shoji - that worked! There was an extra "Iif" in the "UseCount" formula, but once I got rid of that, we were good to go!!

Thanks too for the "Nz" tip. That was one of my "holes", which has now been plugged!

 

[shoji]

No problem.

S.