Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2013-01-29
9
Medium Priority
?
421 Views
Last Modified: 2013-01-29
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?
0
Comment
Question by:DJPr0
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 800 total points
ID: 38831296
>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
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 1200 total points
ID: 38831420
Use Nz:

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

/gustav
0
 

Author Comment

by:DJPr0
ID: 38831464
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:DJPr0
ID: 38831510
/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
 
LVL 61

Expert Comment

by:mbizup
ID: 38831564
(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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38831626
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
 

Author Closing Comment

by:DJPr0
ID: 38831666
Thanks to everyone!

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

Expert Comment

by:Gustav Brock
ID: 38831686
You are welcome!

/gustav
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38831776
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question