If [ComboBox] Is Null Statement in VBA/Access 2007

Question

Hi all,

I have a form with 1 combo box (YearToBeBuilt) and 2 text box fields (Cost and YearofExpenditureCost). All controls are linked to a main table and the table is updated once the selections/entries have been made on the form.

I have written a procedure in VB called ReCalcIt()which performs the following procedure when called:

Private Sub ReCalcIt()
If Me.YearToBeBuilt = "" Then
Me.YearofExpenditureCost = Me.Cost
Else
Me.YearofExpenditureCost = Me.Cost * (1 + 0.031) ^ (Me.YearToBeBuilt - 2010)
End If
End Sub

When I wrote this I thought that this would do the following:

If the combo box [YearToBeBuilt] is blank (e.g.-no selection made) Then the textbox [YearOfExpenditureCost] will return the value of the textbox [Cost]. Else, the calculation for YearofExpenditureCost is performed.

But this is not working the way it should

What am I doing wrong? I am a VBA n00b so perhaps my syntax is incorrect?

Thanks in advance!

Answer 1

Try it with

If Len(Me.YearToBeBuilt & vbNullString) = 0

so the code will look like this:

Private Sub ReCalcIt()
If Len(Me.YearToBeBuilt & vbNullString) = 0 Then
Me.YearofExpenditureCost = Me.Cost
Else
Me.YearofExpenditureCost = Me.Cost * (1 + 0.031) ^ (Me.YearToBeBuilt - 2010)
End If
End Sub

Answer 2

The usual way is to say:

If IsNull(MyControl) Then

See also: http://stackoverflow.com/questions/3751233/access-vba-if-form-value-null-then-run-query-else-end-if-not-doing-anythi/3751399#3751399