Can't get date constants to function

[JKoyis]

Using Access 2002:

I'm having trouble getting the date constants to work right. I'm trying to write code to find the most recent date when a student had a health checkup, compare this with the current date, and output their name if it was too long ago. However the constants for getting the current date don't seem to be functioning.

The code is in a form_load event. To test the date stuff I put the following line of code right at the start of the procedure.

MsgBox ("The year is: " & Year(Date))

This returns 'invalid use of null'

I tested the same code on a different form in a command button onclick event and it works fine. I also have working date code when the database loads. Can anyone tell me what the problem is please?

[mattkorguk]

I realise this doesn't help you but I just put your line of code in 2003 on a forms 'on load' event and it worked fine.
Matt

[missinglinq]

Could we see your entire Form_Load event code?

[JKoyis]

Could we see your entire Form_Load event code?

Okay, here it is. Before reading, I've tested the two lines I already posted on command buttons on other forms in the database and they work fine. However on this particular form they don't work, not only in the Form_Load event but with the two lines in a seperate command button. I've also tried creating a new form (without a load event) adding a command button with those two lines - this still doesn't work - so before you read this code (which is based on another module and may contain further hideous bugs) bear in mind it may not be the source of the problem.

Private Sub Form_Load()

'Outstanding health check algorythm

'for each student
'Go through health checks for each student
'identify most recent healthcheck
'compare its date to current date
'if more than 1 year difference
    'output name
    
'
MsgBox ("start")
MsgBox ("The year is: " & Year(Date))


Dim outputString As String

Dim labelHeight As Integer

Dim studentRecordCount As Integer
studentRecordCount = DCount("[ID]", "tblStudents")

If Form_frmInterface.chkShowAlumni.Value = 0 Then
    studentRecordCount = DCount("[ID]", "tblStudents", "[Alumni] = 0")
End If

MsgBox ("student record count is: " & studentRecordCount)

Dim stDocName As String
        Dim stLinkCriteria As String
        
        'make sure the form is closed before we open it with specific criteria!
        'if not, then it may already be opened, unfiltered and bug prone

        
        stDocName = "frmStudentEntryNew"
                
       If Form_frmInterface.chkShowAlumni.Value = 0 Then
        stLinkCriteria = "[Alumni]=" & 0
       End If
       
DoCmd.OpenForm stDocName, , , stLinkCriteria 'open the student record form

For countStudents = 1 To studentRecordCount
    DoCmd.GoToRecord acDataForm, "frmStudentEntryNew", acGoTo, countStudents
    Dim healthCheckRecordCount As Integer

    Dim temp As Integer

'should this be here????
Next countStudents

temp = Form_frmStudentEntryNew.txtID.Value
    'pulls the number of healthCheck with current student id from table
    healthCheckRecordCount = DCount("[HealthCheckID]", "tblHealthCheck", "[StudentID] = " & temp)
    
    'store the most recent date. Initialize it as something silly so
    'if we dont find a more recent one we definitely require a new health check
    Dim latestHealthCheck As Date
    latestHealthCheck = 1 / 1 / 1901
    
    If healthCheckRecordCount > 0 Then
        stDocName = "frmHealthCheckTables"
        stLinkCriteria = "[StudentID]=" & Form_frmStudentEntryNew.txtID.Value
        DoCmd.OpenForm stDocName, , , stLinkCriteria
            
        'go through the charges
        For countChecks = 1 To healthCheckRecordCount
            DoCmd.GoToRecord acDataForm, "frmHealthCheckTables", acGoTo, countChecks
        
            'compare the most recent charge to the current date
            'stores the most recent date it finds in latestHealthCheck
            If Year(latestHealthCheck) < Year(Form_frmHealthCheckTables.Date.Value) Then
                latestHealthCheck = Form_frmHealthCheckTables.Date.Value
            End If
        Next countChecks
            
        'if latestHealthCheck was a long time ago
        'output the kids name - add it to output string
        
        Dim currentDate As Date
        Dim peskyYear As Date
        
       
        
        MsgBox ("start")
        MsgBox ("The year is: " & Year(Date))
        
        MsgBox (peskyYear)
        MsgBox (Date)
        currentDate = Date
        MsgBox (Year(currentDate))
        MsgBox (Year(latestHealthCheck))
        If ((Year(currentDate) - Year(latestHealthCheck)) > 1) Then
            'output the kids name
            outputString = outputString & Form_frmHealthCheckTables.txtTempName.Value
            outputString = outputString & vbCrLf
            labelHeight = labelHeight + 180
            labelHeight = labelHeight + 180
        End If
            
    End If


Dim heightModifier As Integer
heightModifier = 400

labelHeight = labelHeight + heightModifier 'whack a bit of space on for final output lines
lblOutput.Height = labelHeight
Form_frmSuperOutstandingPayments.Detail.Height = (labelHeight + 550)
Form_frmOutstandingHealthChecks.Detail.Height = (labelHeight + 550)


lblOutputArea.Caption = outputString
Form_frmHealthCheckTables.SetFocus
DoCmd.Close

End Sub

[JKoyis]

I think I may have found the source of the problem, I have a field in one of my tables called 'Date' probably conflicting with the Date constant.

[missinglinq]

You need to read this article about using Reserved Words as object names, especially using Time() and Date():

http://www.thescripts.com/forum/thread659167.html

You might also check to make sure you haven't accidentally reset your system clock!

Good Luck!

[JKoyis]

You need to read this article about using Reserved Words as object names, especially using Time() and Date():

http://www.thescripts.com/forum/thread659167.html

You might also check to make sure you haven't accidentally reset your system clock!

Good Luck!

Yes, the problem was one of the fields on the form was called 'Date'. I renamed it and it works fine. Thanks all.