Solved

Change date field to NULL

Posted on 2011-02-28
8
334 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
[X]
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
  • 4
  • 3
8 Comments
 
LVL 50

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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
LVL 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 35002271
You are welcome!

/gustav
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

728 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