?
Solved

Change date field to NULL

Posted on 2011-02-28
8
Medium Priority
?
335 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 51

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
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
LVL 51

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 51

Accepted Solution

by:
Gustav Brock earned 2000 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 51

Expert Comment

by:Gustav Brock
ID: 35002271
You are welcome!

/gustav
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 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