Solved

Change date field to NULL

Posted on 2011-02-28
8
326 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

758 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