Date function returns null

[TheComputerDude]

I just encountered a problem where the built in date function in VBA shows that DATE is NULL when in debug mode. The NOW function still works however. Does anyone have an idea what might be causing this? I've never had this problem with the DATE function before. My thought is that it might be a sign of a dead battery on the motherboard.

 

[PHV]

Any chance you have a field named date ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheComputerDude]

I have no global variables and here is the code from the form. When in debug, I pointed to the line that I indicated with a --> and DATE was displaying NULL not the system date.


Option Compare Database
Option Explicit

Dim strfilter As String

Private Sub cmdButtonApplyFilter_Click()
Me.Filter = strfilter
Me.FilterOn = True
End Sub

Private Sub Form_Load()
Dim dtmDate As Date

--> dtmDate = Month(Date) & "/1/" & Year(Date)
dtmDate = dtmDate - 7
strfilter = "(Not Transfers.Complete=-1) AND (Transfers.Date > #" & dtmDate & "#)"
Me.Filter = strfilter
End Sub

 

[PHV]

You've not answered my question.
Anyway what happens if you replace Date with VBA.Date ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

And the answer about the CMOS battery being dead; no! If the battery were dead the function Now() wouldn't work either!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[CaptainD]

Not really fixing the initial problem but as far as how you are doing your date, I use this method

Dim dtmDate As Date
dtmDate = Format(Date, "mm/1/yyyy")
MsgBox dtmDate

 

[MajP]

You dimension dtmDate as a date,

Dim dtmDate As Date
but this sure does not return a date:
dtmDate = Month(Date) & "/1/" & Year(Date)
it returns a string because
integer & string & integer

the date #10/1/2007# is stored as the integer 39356, because it is 39356 days since 12/30/1890 the base date. You return a value that is a string "10/1/2007" not a date.

 

[MajP]

so here is an example:
Public Sub testsub()
Dim dtmDate As Date
Dim dtmDate2 As String
dtmDate = DateSerial(Year(Date), Month(Date), 1)
Debug.Print Format(dtmDate, "mm/dd/yyyy")
dtmDate2 = Month(Date) & "/01/" & Year(Date)
Debug.Print dtmDate2
End Sub

output
10/01/2007
10/01/2007

 

[missinglinq]

Then why does

Dim dtmDate As Date

dtmDate = Month(Date) & "/1/" & Year(Date)

If IsDate(dtmDate) Then
  MsgBox dtmDate
End If

popup the message box with dtmDate?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheComputerDude]

Here at work, we have proved that the CMOS battery could be dead and still have the correct time in Windows. How? Most networks have a timeserver that sets the time for computers if they get out of sync, so the CMOS battery could still have a role in this. Next, VB allows dates to be set as strings if they are in a valid format. I've never had problems in the hundreds of times this has been done. The issue here has to do with a single computer (two other ones are using the same code and they do not have the problem). I know the date function sets or retrieves the date in CMOS, but where does the NOW function get its information from? I know the help says that it returns the current system date and time, but I'll bet it is actually pulling the date from Windows and not hitting CMOS directly. I'm investigating that now.