Calculating a date within an IF clause; Experiencing Invlaid Use of Null error

I have the following VBA code used within a form to update a table.

After I select the type of record, "Advancement" or "Reappointment" in a list box named "lstEval" I have to enter the reporting period for the evaluation in an Unbound Text Box - "txtRptPD".

After the date is selected the two unbound txt boxes update with the month beginning "txtRptBeg" and month ending "txtEnd" of the evaluation period.

I am getting an error:

run Time Error # 94

Invalid use of null

I am guessing that the value of the "lstEval" box is  being evaluated as a null.



Below is the code for the lstEval after update and the txtRptPd after change:

Private Sub lstEval_AfterUpdate()
 Dim vType As String
     vType = Me.LstEval.Column(0)
 End Sub

Private Sub txtRptPD_Change()


 If vType = "Advancement" Then
     Me.txtRptBeg = DateSerial(Year(DateAdd("m", -7, Me.txtRptPD)), Month(DateAdd("m", -7, Me.txtRptPD)), 1)
     Me.txtEnd = DateAdd("d", -1, DateSerial(Year(Me.txtRptPD), Month(Me.txtRptPD), 1))
 Else
     Me.txtRptBeg = DateSerial(Year(DateAdd("m", -25, Me.txtRptPD)), Month(DateAdd("m", -25, Me.txtRptPD)), 1)
     Me.txtEnd = DateAdd("d", -1, DateSerial(Year(Me.txtRptPD), Month(Me.txtRptPD), 1))

End If

End Sub

Thanks for your help

Glen
GPSPOWAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
What is the rowsource query for lstEval?

A quick way to determine what is causing the error is to use a messagebox (see the inline notes):

Private Sub lstEval_AfterUpdate()
 Dim vType As String
     msgBox vType &  ""  '<--- Does this display a value?
     vType = Me.LstEval.Column(0) 
 End Sub

Open in new window


You can *handle* nulls to avoid that error like this...

Private Sub lstEval_AfterUpdate()
 Dim vType As String
     vType = trim(Me.LstEval.Column(0) & "")
 End Sub

Open in new window


Private Sub txtRptPD_Change()

 dim dtStart as date
dim dtEnd as Date
dtStart = NZ(Me.txtRptPD, #1/1/1900#)
dtEnd = NZ(Me.txtRptPD, #1/1/2100#)
 If vType = "Advancement" Then
     Me.txtRptBeg = DateSerial(Year(DateAdd("m", -7, dtStart)), Month(DateAdd("m", -7, dtStart)), 1)
     Me.txtEnd = DateAdd("d", -1, DateSerial(Year(dtEnd), Month(dtEnd), 1))
 Else
     Me.txtRptBeg = DateSerial(Year(DateAdd("m", -25, dtStart)), Month(DateAdd("m", -25, dtStart)), 1)
     Me.txtEnd = DateAdd("d", -1, DateSerial(Year(dtEnd), Month(dtEnd), 1))

End If
End Sub

Open in new window

0
 
GPSPOWAuthor Commented:
I will try this and get back with you.

Thanks

Glen
0
 
GPSPOWAuthor Commented:
Your solution solved the null error.

I need to tweak the code to calculate the txtBegPD and txtEnd values.  

Thanks

Glen
0
 
mbizupCommented:
Glad to help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.