locked
If [Field] is null then RRS feed

  • Question

  • This code works perfeclty, but when I try to add an If Then statement, I get an error.

    The desire is that the If Then will look at the current value of [Initiator]

    if it is null, the If Then statement will replace it with the value from the following code

    if it is not null, the If Then statement will leave the value unchanged

     

    Private Sub Ticket_Paste_AfterUpdate()
    If [Initiator] Is Null Then
    [Initiator] = Trim(Mid((Trim(Mid([Ticket_Paste], InStr([Ticket_Paste], "Submission MN_CanWeServeIS") - 50, 43))), InStr((Trim(Mid([Ticket_Paste], InStr([Ticket_Paste], "Submission MN_CanWeServeIS") - 50, 43))), "M") + 1, 50))
    DoCmd.RunCommand acCmdRefreshPage
    End If
    End Sub

    Friday, April 1, 2011 1:52 AM

Answers

  • Ok, after an hour of banging my head against the wall, I figured it out on my own.  (For what it's worth)

    This workds:             If IsNull(Forms!Tickets![Initiator]) Then

    This does not work:   If [Initiator] Is Null Then

    Why:                        Now that is a question to yet to be provided

    Good code:

    If IsNull(Forms!Tickets![Initiator]) Then
    [Initiator] = Trim(Mid((Trim(Mid([Ticket_Paste], InStr([Ticket_Paste], "Submission MN_CanWeServeIS") - 50, 43))), InStr((Trim(Mid([Ticket_Paste], InStr([Ticket_Paste], "Submission MN_CanWeServeIS") - 50, 43))), "M") + 1, 50))
    DoCmd.RunCommand acCmdRefreshPage
    End If
    End Sub

     

    You guys saved my life several times, so maybe this example might jut help someone else.


    • Marked as answer by Mark Matzke Friday, April 1, 2011 3:05 AM
    Friday, April 1, 2011 3:04 AM

All replies

  • Ok, after an hour of banging my head against the wall, I figured it out on my own.  (For what it's worth)

    This workds:             If IsNull(Forms!Tickets![Initiator]) Then

    This does not work:   If [Initiator] Is Null Then

    Why:                        Now that is a question to yet to be provided

    Good code:

    If IsNull(Forms!Tickets![Initiator]) Then
    [Initiator] = Trim(Mid((Trim(Mid([Ticket_Paste], InStr([Ticket_Paste], "Submission MN_CanWeServeIS") - 50, 43))), InStr((Trim(Mid([Ticket_Paste], InStr([Ticket_Paste], "Submission MN_CanWeServeIS") - 50, 43))), "M") + 1, 50))
    DoCmd.RunCommand acCmdRefreshPage
    End If
    End Sub

     

    You guys saved my life several times, so maybe this example might jut help someone else.


    • Marked as answer by Mark Matzke Friday, April 1, 2011 3:05 AM
    Friday, April 1, 2011 3:04 AM
  • The syntax is:
          If(IsNull([Initiator])) Then
    Friday, April 1, 2011 3:05 AM
  • Simple explanation.

    "xx is Null" is an SQL test.

    "IsNull(xx)" is a VBA test.  If you are working in VBA code the SQL test is not available.  Since Access allows you to use VBA tests in SQL (as long as you are using an mdb or accdb or an odbc connection) you can use IsNull in an SQL statement.  IsNull(xx) in an SQL statement is not as efficient as "xx is Null", but it does work.


    John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County
    Friday, April 1, 2011 2:14 PM
  • Many years later, this saved me from insanity...

    Thanks Mark!

    Friday, January 10, 2020 6:21 PM