Not Null

[kitty77]

I have a report (onload) that has four fields which are all NOT visible. I would like to make each field visible if the condition is met.

If IsNotNull([Mresults1]) And IsNull([Mresults2]) Then [Mresults1].Visible = True
If IsNotNull([Mresults2]) And IsNull([Mresults3]) Then [Mresults2].Visible = True
If IsNotNull([Mresults3]) And IsNull([Mresults4]) Then [Mresults3].Visible = True
If IsNotNull([Mresults4]) Then [Mresults4].Visible = True

Not sure I'm using the IsNotNull and IsNull properly.

Thanks.

 

[June7]

IsNotNull() is not an intrinsic function. Did you write a custom function?

If Not IsNull([Mresults1]) And IsNull([Mresults2]) Then [Mresults1].Visible = True

Are you pulling multiple records in this report? Need to determine what will happen with each record when condition True or False? What you have will set all records the same. Consider:

Me.Mresults1.Visible = Not IsNull([Mresults1]) And IsNull([Mresults2])

And code would have to be in the Format event of Detail section which only executes for Print or PrintPreview.

Alternative is IIf() expression in textbox that just returns Null.

 

[kitty77]

I'm pulling one record only.

 

[kitty77]

That worked! Thanks!

 

[The_Doc_Man]

Just to simplify... this could also work.

Mresults1.Visible = IsNull([Mresults2]) AND ( NOT IsNull([Mresults1])
Mresults2.Visible = IsNull([Mresults3]) AND ( NOT IsNull([Mresults2])
Mresults3.Visible = IsNull([Mresults4]) AND ( NOT IsNull([Mresults3])
Mresults4.Visible = Not IsNull([Mresults4])

Your IF/THEN sequences were NOT WRONG. But this is a slightly more efficient way based on the theory that you have to compute the T/F value of the expressions to power the IF decision anyway - so instead use it directly in assigning the T/F result.

 

[Pat Hartman]

Whether you are pulling one record or a million, put the code in the logically correct event which will ALWAYS work rather than an event which will SOMETIMES work.

Put the corrected code into the Form's Current event. You put a time bomb in the form when you use the Load event which runs ONLY once when you should be using the Current event because it runs for each record. Learn what the form and control events are each intended to do and what causes them to fire so you know how to use them correctly. Yes, you will get away with it "this" time but WHY???? Why not just learn how to do it right?

 

[kitty77]

Just to simplify... this could also work.

Mresults1.Visible = IsNull([Mresults2]) AND ( NOT IsNull([Mresults1])
Mresults2.Visible = IsNull([Mresults3]) AND ( NOT IsNull([Mresults2])
Mresults3.Visible = IsNull([Mresults4]) AND ( NOT IsNull([Mresults3])
Mresults4.Visible = Not IsNull([Mresults4])

Your IF/THEN sequences were NOT WRONG. But this is a slightly more efficient way based on the theory that you have to compute the T/F value of the expressions to power the IF decision anyway - so instead use it directly in assigning the T/F result.

Thank You!

 

[June7]

Pat, OP specified code for a report, not a form.

 

[Pat Hartman]

Then use the Format() event. You need an event that runs for EVERY record, not just once when the form loads.

 

[Cronk]

The OP also said the report had only one record to display

 

[Pat Hartman]

The OP also said the report had only one record to display

WHO CARES? WHY would you put code in an event that ONLY works under certain circumstances when you KNOW there is an event which will ALWAYS work? All that does is lead novices to future problems. The next time the OP has to solve this problem, he'll look at this example with YOUR suggestion and copy it for the new report and wonder why it doesn't work.

Yes, Access is indeed flexible and in some cases, code will work in various events. But, if you understand the object model, you will KNOW that there is always a best (or at least better) option and you should always use that method even when there is a different event that will work in this specific case. But go right ahead and recommending the Open event rather than the Current (form) or on Format(report) events. Don't bother to understand why specific events exist.

The event model is NOT random. Events are technically subroutines. The Access mainline code that is executing and making the form actually work, has "hooks". These are places in the code behind the form (that you NEVER see) where events are processed and if you have provided code for the event that was raised, Access calls your sub for that event. It is up to you to understand which event is most appropriate for what you need to make happen.

The Open event is NOT the correct event to use for code that is dependent on the value of a record since it only runs ONCE when the form is opened. Yes, it works if there is only one record to display but why would you ever recommend this as an actual solution?