MS Access How can I check for a text value in a null value field?

This code works fine until I added the piece in bold:

Private Sub Project_Name_AfterUpdate()


      If "" & Me.ProjectName <> "" And IsDate(Me.AendDate) = False And Me.Status <> "Cancelled" Then
        Me.Status = "Working"
    End If
End Sub

This statement doesn't work due to a null value in the Status Field.
(Only works with existing text in the Status field)

How can I check for text "Cancelled" in a null value field?
DJPr0Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Use Nz:

  If Nz(Me.ProjectName) <> "" And Not IsDate(Me.AendDate) And Nz(Me.Status) <> "Cancelled"  Then
   Me.Status = "Working"
  End If

/gustav
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>How can I check for text "Cancelled" in a null value field?
Couple of ways..
IsNull(Me.Status) will return True if Null, False if not null
Nz(Me.Status, "foo") will return the Me.Status if not null, and whatever value you want to slam in here (aka foo) if null
0
 
DJPr0Author Commented:
Still not working:
(Working will not populate the status field on after update)

Private Sub Project_Name_AfterUpdate()
If "" & Me.ProjectName <> "" And IsDate(Me.AendDate) = False And Nz(Me.Status, "Cancelled") = False Then
        Me.Status = "Working"
End If
End Sub

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
DJPr0Author Commented:
/gustav

This isn't working: (only works if Status has text)

 If Nz(Me.ProjectName) <> "" And Not IsDate(Me.AendDate) And Nz(Me.Status) <> "Cancelled"  Then
   Me.Status = "Working"
  End If
0
 
mbizupCommented:
(No points, please)

Take another look at Jim's post.

You don't have the syntax quite right.  It should be:

 If Nz(Me.ProjectName) <> "" And Not IsDate(Me.AendDate) And Nz(Me.Status,"") <> "Cancelled"  Then
   Me.Status = "Working"
  End If

Open in new window

0
 
Gustav BrockCIOCommented:
Try with:

  If Nz(Me.ProjectName) <> "" And Not IsDate(Me.AendDate) And Nz(Me.Status, "Empty") <> "Cancelled"  Then
   Me.Status = "Working"
  End If

/gustav
0
 
DJPr0Author Commented:
Thanks to everyone!

/gustav did work (when I put the code in the right form!)
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.