Solved

Date displaying 12:00 AM when null

Posted on 2004-09-24
10
594 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
ID: 12144409
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
ID: 12146874
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
ID: 12149741
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
ID: 12149885
   If NZ(RsCatalogInfo!RowLastUpdtDtTi,"") <> "" Then

Alan
0
 

Author Comment

by:zachvaldez
ID: 12161133
Is NZ available in VB6? or access olnly
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 26

Expert Comment

by:Alan Warren
ID: 12161236
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
ID: 12162377
Member not found is the error message for NZ
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12164577
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
ID: 12184432
A function added to the string in the array??
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 125 total points
ID: 12186315


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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

862 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

23 Experts available now in Live!

Get 1:1 Help Now