Solved

Date displaying 12:00 AM when null

Posted on 2004-09-24
10
604 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

832 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