asked on

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?

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D J

ASKER

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

D J

ASKER

/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

mbizup

(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

Gustav Brock

Try with:

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

/gustav

D J

ASKER

Thanks to everyone!

/gustav did work (when I put the code in the right form!)

Gustav Brock

You are welcome!

/gustav

Jim Horn

Thanks for the split. Good luck with your project. -Jim