checking for null

[johnlute]

I've been struggling with a problem for a couple days now and am on
the verge of resolving it - I think. I hope you can help get me over
the hump!

I have a form with a query record source. It has an alias with the
expression:
IDAreasq: IIf([IDWdia]="ID Diameter",Null,IIf([IDLength]+[IDWidth]+
[IDHeight],Null,IIf(IsNull([IDLength])+IsNull([IDWidth])+IsNull
([IDHeight]) In (0,-1),Nz([IDLength],1)*Nz([IDWidth],1)*Nz([IDHeight],
1),Null)))

The form is filled in real time therefore this alias will populate
with a numeric value given the expression. If the expression returns a
numeric value then I want certain controls on the form to be enabled.
If the expression doesn't return a numeric value then I want those
same controls on the form to be disabled.

After much fiddling and other help on this newsgroup I've arrived at:
Me.IDAreasqinmin.Enabled = Not Len(Me.IDAreasq & vbNullString) <= 0

This is placed in the form's OnCurrent Event and works ONLY when I
enter a new record and then save it by navigating from the record and
then returning to it. I want to be able to enter values into
Me.IDAreasqinmin (and other fields not mentioned here) "on the fly"

Is this possible to resolve?

 

[johnlute]

Hi, Bruce. Thanks for the response.

Is IDWdia a text field that could contain the literal value "ID Diameter"?
That is what you are testing for in the first IIf.
Yes.

The second IIf has this:

IIf([IDLength]+[IDWidth]+[IDHeight],Null,...

However, there is no logical test.  It is like saying "If 1 + 2 + 3, Nothing,
otherwise do something."  You need to specify the logical test:  If 1+ 2 + 3

2, or something.

IsNull is for VBA.  In SQL, which is what you are using, it is "Is Null".
Also, the + sign is a math operator, or in some cases a concatenation
operator, but it is not a substutute for "And".

IIf([IDLength] Is Null And [IDWidth] Is Null And [IDHeight] Is Null, ...

I don't see what you are trying to accomplish with "In (0,1)"

If length, width, and height are all null you are looking for the value 1..
There is no need to go through Nz for all three values, since you won't reach
that part of the code unless all three are null.  If you do reach that part
of the code, the calculation will always result in a value of 1.

It may help if you describe in non-Access language what you are trying todo.

I really don't have a problem with the IDAreasq alias expression
however I can see that it might help to explain things.

Essentially, I'm trying to calculate the Area of three potential
dimensional fields: [IDLength], [IDWidth], and [IDHeight]. Of course,
an Area is the multiplication of two values.

The table that houses these three fields is used to record multiple
types of "entities" such as cups, boxes, bottles, bags, paper,
slipsheets, film, bands, etc. Some of these are 3-dimensional and some
are 2-dimensional. A 2-dimensional entity may have values in any two
of the three fields. A 3-dimensional entity may have values in all
three fields OR a value in [IDWidth] (if it's ROUND in shape) and a
value in [IDHeight]. In this instance, the value in [IDWidth] is
multiplied with itself and then [IDHeight]. Of course, this is
triggered by the "labeling" of "ID Diameter".

So...some entities will have dimensional values that result in an Area
and some will have dimensional results that result in a Cube.

As for the other stuff - I believe I had to throw them in there in
order to keep from displaying peculiar things in the form's controls.

Currently, the way I have it setup works just fine. Perhaps it's not
the most efficient but I'd rather not tinker with it since it ain't
broke.

Hope that helps!

 

[johnlute]

Hi, Bruce.

Resolved - FINALLY! I'm very frustrated that this didn't occur to me
before. I simply added the following to the AfterUpdate Events of the
IDLength, IDWidth, and IDHeight controls. Tthere were a few more
controls I needed to enable/disable as I mentioned above:

Me.IDAreasqinmin.Enabled = Not Len(Me.IDAreasqinr & vbNullString) <= 0
Me.IDAreasqinmax.Enabled = Not Len(Me.IDAreasqinr & vbNullString) <= 0
Me.IDAreasqftmin.Enabled = Not Len(Me.IDAreasqinr & vbNullString) <= 0
Me.IDAreasqftmax.Enabled = Not Len(Me.IDAreasqinr & vbNullString) <= 0
Me.IDCubecuinmin.Enabled = Not Len(Me.IDCubecuin & vbNullString) <= 0
Me.IDCubecuinmax.Enabled = Not Len(Me.IDCubecuin & vbNullString) <= 0
Me.IDCubecuftmin.Enabled = Not Len(Me.IDCubecuin & vbNullString) <= 0
Me.IDCubecuftmax.Enabled = Not Len(Me.IDCubecuin & vbNullString) <= 0

With this in the form's Current Event - it all works now.

Geesh. I spent DAYS on this!!!

Thanks for the help!