If Not IsNull Help - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

If Not IsNull Help

Mark Budinger

3 years ago

Good Afternoon,

I have a field called PO_Part_Comments and it is a text box on my form. I have this After update event set to it

If Not IsNull(Me.PO_Part_Comments) Then
        Me.PO_Edit_Check = True
        Me.PO_Edit_Date = Date

Now this works just fine. What I am trying to do now is, if the text boxed gets cleared out, I would then like the PO_Edit_Check = False & PO_Edit_Date = Null.

On the same after update I tried adding

If Me.PO_Part_Comments = Null Then
    Me.PO_Edit_Check = False
    Me.PO_Edit_Date = Null
End If

Nothing happens. The check and Date do not get removed.

What is the best way do this?

Regards,
Mark

	Adam Schwanz @Reply 3 years ago
It's either doesnt like the null syntax or it's being seen as an empty string instead of null, try this `If IsNull(Me.PO_Part_Comments) or Me.PO_Part_Comments = "" Then`

Mark Budinger OP @Reply

3 years ago

Hi Adam,

Thank you for the reply. The second solution you game me worked perfectly. I used this as the after update

If Not IsNull(Me.PO_Part_Comments) Then
        Me.PO_Edit_Check = True
        Me.PO_Edit_Date = Date
    End If

    If Me.PO_Part_Comments = "" Then
        Me.PO_Edit_Check = False
        Me.PO_Edit_Date = Null
    End If

It is now working the way I need it to.

Thank you again.

Regards,
Mark

	Kevin Robertson @Reply 3 years ago
You can simplify the code: `If Not IsNull(PO_Part_Comments) And PO_Part_Comments <> "" Then PO_Edit_Check = True PO_Edit_Date = Date Else PO_Edit_Check = False PO_Edit_Date = Null End If`

	Mark Budinger OP @Reply 3 years ago
Hi Kevin, I like simplified code. I tried your suggestion and it works perfectly. Thank you so much. Regards, Mark

Kevin Yip @Reply

3 years ago

If PO_Part_Comments is null, then the condition PO_Part_Comments <> "" will be neither true nor false, but null.  In this case, the code still works.  But generally speaking, you want to avoid having a condition evaluate to a null, because that may lead to undesirable results.  To test if a variable is a null or empty string, I use this method:

     If Len(Trim(s & "")) > 0 Then ...

This gets rid of a possibly null value by converting it into an empty string.  Then, if its length is greater than zero, it is neither a null nor an empty string.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View