Solved

Date displaying 12:00 AM when null

Posted on 2004-09-24
10
610 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

860 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