Solved

Change date field to NULL

Posted on 2011-02-28
8
328 Views
Last Modified: 2013-11-27
I need to create a function to evaluate a date if the date meets the criteria then I need to remove that date value from the field.  i have tried various ways to create a null value without success.  NOTE:  this function will be called from w/i a query.

Got any Ideas?

Karen
Public Function CompleteDateChk(ndate As Date)
       
    If IsDate(ndate) Then
        If Year(ndate) <> Year(Date) Then
            ndate = vbNullString
        ElseIf Year(ndate) = Year(Date) And ndate > Date Then
            ndate = ""
        End If
    End If
    ndate = ndate

End Function

Open in new window

0
Comment
Question by:Karen Schaefer
  • 4
  • 3
8 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35001449
This will do if you wish to return the date or Null:
Public Function CompleteDateChk(ndate As Date)
       
Dim varDate As Variant
    If Year(ndate) <> Year(Date) Then
        varDate = Null
    ElseIf ndate > Date Then
        varDate = Null
    Else
        varDate = ndate
    End If
    CompleteDateChk = varDdate

End Function

Open in new window

/gustav
0
 

Author Comment

by:Karen Schaefer
ID: 35001485
thanks for the input, however, it still returns #Error.

K
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35001623
use the nullif function ... if both arguments are the same then a null is returned...

update yourtable
 set thedatecolumn = nullif(thedatecolumn,thedatecolumn)
 where  .... your criteria
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35001635
Still? Since when?

That is likely caused by feeding non-date/time values as the parameter. If so:
Public Function CompleteDateChk(varDate As Variant) As Variant

    If Not IsDate(varDate) Then
        varDate = Null       
    ElseIf Year(varDate) <> Year(Date) Then
        varDate = Null
    ElseIf varDate > Date Then
        varDate = Null
    End If
    CompleteDateChk = varDdate

End Function

Open in new window

/gustav
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Karen Schaefer
ID: 35001764
Ok that worked , however is there a way to converted back to a date on those records where the field actual contains a value.  I tried Cdate, but then I got the #error again.

K
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 35002071
That could be this:
Public Function CompleteDateChk(varDate As Variant) As Variant

    If Not IsDate(varDate) Then
        varDate = Null  
    Else
        varDate =CDate(varDate)
    End If     
    If Year(varDate) <> Year(Date) Then
        varDate = Null
    ElseIf varDate > Date Then
        varDate = Null
    End If
    CompleteDateChk = varDate

End Function

Open in new window

/gustav
0
 

Author Closing Comment

by:Karen Schaefer
ID: 35002193
Thanks for the assist.

Karen
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35002271
You are welcome!

/gustav
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

932 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now