Nz or IsNull Functions

PatAccess

Registered User.
Local time
Today, 06:53
Joined
May 24, 2017
Messages
284
Hello,

I am fairly new at VBA and I am having some issues with the Nz or IsNull functions. They are confusing me.

I have a button on form that opens a report based on a cbo choice.

So here is the code I have for my click event: DoCmd.OpenReport "Rpt_PEByName", acViewPreview, , "EmpID='" & Me.cboFName.Value & "'"

I get very confused when it is time to write these functions, as to where to put the Nz or IsNull functions in case that person has no licenses to renew. and if there is nothing I would like to add a MsgBox saying there is nothing. Should the function go on my report or my form and what should be the expression. I appreciate the help.


Thank you,
 
Hello,

I am fairly new at VBA and I am having some issues with the Nz or IsNull functions. They are confusing me.

I have a button on form that opens a report based on a cbo choice.

So here is the code I have for my click event: DoCmd.OpenReport "Rpt_PEByName", acViewPreview, , "EmpID='" & Me.cboFName.Value & "'"

I get very confused when it is time to write these functions, as to where to put the Nz or IsNull functions in case that person has no licenses to renew. and if there is nothing I would like to add a MsgBox saying there is nothing. Should the function go on my report or my form and what should be the expression. I appreciate the help.


Thank you,

Hi Pat,
generally you would want to check if the passed WHERE argument is null or empty before you send it for execution to a report. You could simply protect against the Null argument by
Code:
 "EmpID='" & Nz(Me.cboFName.Value) & "'"
and then in the report check if you have a "No Data" situation. But the simpler way is to do the check in the sub before opening the report. Like
Code:
If Nz(Me!cboFName) = "" Then 
MsgBox "There is nothing"
Else
DoCmd.OpenReport...
End If

Best,
Jiri
 
Ok I think this is what's creating the problem. In my report I have a format code and it is giving me an error. What am I doing wrong?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.LicName.Visible = False
Me.State.Visible = False

If Nz(Me!LicName) = "" Then
MsgBox "Nothing"
ElseIf Me.LicName = "State PE License" Then
Me.LicText = Me.State
Else
Me.LicText = Me.LicName
End If
 
If you have a report, you would be better of using the On No Data event in the report.

Someone will probably enter a valid name as you are using a combo, but that doesn't mean there will be a report for them. Using On No Data will trap that and allow you to put msg box up and close the report
 
Agree with minty as to the best way forward.
But in answer to your question, you haven't defined what the Nz output should be if the value is null -added in red below
Ok I think this is what's creating the problem. In my report I have a format code and it is giving me an error. What am I doing wrong?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.LicName.Visible = False
Me.State.Visible = False

If Nz(Me!LicName,"") = "" Then
MsgBox "Nothing"
ElseIf Me.LicName = "State PE License" Then
Me.LicText = Me.State
Else
Me.LicText = Me.LicName
End If
 
I thought of that, too, but according to Help, the second argument is optional, and if omitted, actually assumes/returns "".

Linq ;0)>
 
Back to your original question,

Is Null is asking "Has NO value EVER been entered" and is often use for asking logical questions regarding new records. It is the difference between asking "Has NO Appointment EVER been made" VS "Do they not have a current appointment".

Nz() allows you to deal with Null values. If there is a possibility a value would be null, such as said Appointment Date, you can wrap it in Nz() to return a value that would make sense in context.

Example would be;

Code:
if [Appointment_date] Is Null Then Me.Note = "Need to schedule initial examination"

conversely when you are trying to contact people, you may have a query ordered by [Dt_Contact], a computed field

Code:
Dt_Contact: Nz([Appointment_Date],now())

so you have anyone who's NEVER had an appointment first in your list.
 
Mark is correct, but left out something from your original post.

Nz or IsNull functions. They are confusing me.

Quick summary:

You use IsNull(A) if you want to know (TRUE/FALSE) whether A is null.

You use Nz(A,B) in a case where you need a value. Nz is internally SIMILAR to (but I don't guarantee IDENTICAL WITH):

Code:
Public Function Nz(A as Variant, B as Variant) as Variant
  If IsNull(A) Then
    Nz = B
  Else
    Nz = A
  End If
End Sub

Mark brought in another relevant construct but it has a couple of "gotcha" cases.

If X Is Null Then... would be used interchangeably with If IsNull(X) Then... even though one is a function and one is a special syntax case for VBA. BUT if X happens to be an OBJECT rather than a simple variable, you would use a different version of the "IS" clause - If X Is Nothing Then ... You would use this when the object has not yet been assigned to a reference via SET X = {some object or object-creating function}. That is, the IF clause would trigger if X is de-referenced i.e. does not currently point to anything.

There are other functions you could use - IsEmpty(X) which applies to X as a string. (Strings can be empty and not null.) In subroutines where you have formal but optional arguments, IsMissing(X) would be true if the subroutine had an optional 4th argument X in the declaration but you used only 3 arguments in the actual invocation (and didn't include a comma as a placeholder).

Note also that declared (Dim, Public, Private) variables cannot be null with the exception of Variant types. However, recordset fields CAN be null, particularly for OUTER JOIN cases where the parent record did not have a child record. Since a Variant data type CAN be an object, a Variant could also be Nothing.

Just FYI regarding your first post, when dealing with values like this: Me.cboFName.Value

If this is used in the class module for the form, Me. is optional if cboFName is a control on the form. If the Bound Column for that (presumed) combo box is the column holding the desired value to represent the combo box, you can also omit .Value as well, because .Value is the default in that context. The only time you would need special syntax would be if there were a space in the control name, in which case you would have to use square brackets as [cboFName]. But what you wrote was NOT wrong - just longer than it needed to be.

Hope this clarifies the related functions for variable statuses Null, Empty, Missing, etc.

Here is a nice article from a reputable source we often reference. Allen Browne's work is generally top-notch and at its worst contains a FEW assumptions. At his best, Allen's work is above reproach.

http://allenbrowne.com/vba-NothingEmpty.html
 
Last edited:
Thank you very much for all the explanation. I've learned a lot and have a better understanding of the the Null and Nz.
It works now!
 

Users who are viewing this thread

Back
Top Bottom