Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Change date field to NULL

Posted on 2011-02-28
8
331 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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