Solved

Date displaying 12:00 AM when null

Posted on 2004-09-24
10
577 Views
Last Modified: 2013-12-25
       
Date displaying 12:00 AM when null  and not "N/A"

    If IsDate(RsCatalogInfo!RowLastUpdtDtTi) Then
                g_CatalogInfo(g_iCatPoint).LastUpdatedTime = RsCatalogInfo!RowLastUpdtDtTi
            Else
              g_CatalogInfo(g_iCatPoint).LastUpdatedTime = "N/A"
            End If
0
Comment
Question by:zachvaldez
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Expert Comment

by:fds_fatboy
Comment Utility
That appears to be a statement, not a question ;-) What exactly is your question and what datatypes are you using? Have you got any error handling?

PS: As a matter of style, RsCatalogInfo!RowLastUpdtDtTi is not nice RsCatalogInfo.Fields("RowLastUpdtDtTi").Value is nicer.
0
 

Expert Comment

by:XgenX
Comment Utility
Two comments, hope helps.

You are reading a datetime value out of SQL database? Null datetime appears as something like '1800-01-01 12:00:00'. Instead of IsDate test for this value (check value, I'm recalling from memory) and make sure it is never used as an actual date elswhere.

And I don't know reason for fds_fatboy style suggestion but if you follow it you will lose some intellisense.
0
 
LVL 10

Expert Comment

by:fds_fatboy
Comment Utility
It sounds like yiu are hoding zeroes on the database instead of nulls.

The style thing should not lose you any intellisense because using bang notation - as it is late bound - as is dot notation.
The bang notation is a VB anomaly which is shorthand for the dot notation. In VBA it is less efficient to use bang notation because the interpreterwill convert it to dot notation before interpreting that. Bang notation in VB involves using the Default Collection/Property. The concept of Default Properties has caused problems since VB went object based. Anyway, it's probably my problem - not yours.

It is purely a style thing and I wouldn't lose any sleep over it.
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
   If NZ(RsCatalogInfo!RowLastUpdtDtTi,"") <> "" Then

Alan
0
 

Author Comment

by:zachvaldez
Comment Utility
Is NZ available in VB6? or access olnly
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Zac,


Its available in vb6 too, belongs the Visual Basic Library and is exposed in Access by setting a reference to Visual Basic for Applications (VBA)

Alan
0
 

Author Comment

by:zachvaldez
Comment Utility
Member not found is the error message for NZ
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Zac,

Heres one I use with vbscript in ASP, because NZ function is not supported in Vbscript

Public Function hNz(vValue, vValueIfNull)
  If IsNull(vValue) Then
    If IsNull(vValueIfNull) Then
      hNz = ""
    Else
      hNz = vValueIfNull
    End If
  Else
    hNz = vValue
  End If
End Function
0
 

Author Comment

by:zachvaldez
Comment Utility
A function added to the string in the array??
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 125 total points
Comment Utility


Hey Zac,

first of all let me apologise, seems NZ is not a member of the VB library, it is a member of the application class of the Access library.

Variables declared of type Date are never null, they are initialised to "12:00 AM", they are never empty, and they pass the the IsDate test. So the best thing you could do here is to check for the default initialised value of 12:00 AM



Private Sub Command1_Click()
  Dim dtMyTime As Date
 
  ' Date time variable is never empty
  If IsEmpty(dtMyTime) Then: dtMyTime = Now()
 
  ' Date time variable is never null
  If IsNull(dtMyTime) Then: dtMyTime = Now()
 
  ' Date time variable always isDate
  If Not IsDate(dtMyTime) Then: dtMyTime = Now()
 
  ' Date time variable initialized at 12:00 AM
  If dtMyTime = "12:00 AM" Then: dtMyTime = Now()
  Debug.Print Format(dtMyTime, "dd-mmm-yyyy hh:nn:ss am/pm")
 
 
End Sub

Regards Alan
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

771 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

11 Experts available now in Live!

Get 1:1 Help Now