If [Field] is null then

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

Answer 1

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.

Answer 2

The syntax is:
      If(IsNull([Initiator])) Then

Answer 3

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

Answer 4

Many years later, this saved me from insanity...

Thanks Mark!