Solved

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

Posted on 2013-12-08
4
284 Views
Last Modified: 2013-12-08
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
0
Comment
Question by:GPSPOW
  • 2
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39704928
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
 

Author Comment

by:GPSPOW
ID: 39704932
I will try this and get back with you.

Thanks

Glen
0
 

Author Closing Comment

by:GPSPOW
ID: 39704972
Your solution solved the null error.

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

Thanks

Glen
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39704981
Glad to help :)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now