CODEWITHSUNDEEP
vbams-access-2007
user3088621
user3088621

Reputation: 11

Empty date/time field in a query using a VBA Function outputs #Error

In a query I have (among others) two columns: start_date_time and end_date_time. In the next column I want to calculate the duration ( = end_date_time - start_date_time). For this calculation I am using VBA (I use VBA because I calculate the duration only if it falls between beggining date and finish date arbitrary defined by the user in two fields in the main form).

Now when there are dates and times defined in the columns start_date_time and end_date_time the calculation is OK. The problem comes up when these two fields are empty. In this case I get the "#error" sign in the "duration" field.

I already tried to make a condition to get rid of this "#error" and change it to 0. I tried to find out wheather the empty field is a null or missing or empty but with no success. Whenever there is no data in these fields I get the "#error" sign in the field of the "duration" column.

Am I missing something? How can I change this "#error" sign to e.g. 0. Thank you in advance

The function I use for the calculation is:

Function DurationInterval(BegginingDate As Date, FinishDate As Date, start_date_time As Double, end_date_time As Double) As Double


If start_date_time >= BegginingDate And start_date_time <= FinishDate Then
      DurationInterval = end_date_time - start_date_time
End If


End Function

then I call this function in the builder in the query with:

trajanje: DurationInterval([Forms]![Glavna stran]![Besedilo39];[Forms]![Glavna stran]![Besedilo43];[Zacetek sestanka];[Konec sestanka])

Upvotes: 0

Views: 1159

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123429

You are receiving #Error results because your function declaration specifies your times as Double, so they cannot receive Null values. If you want to be able to pass Null values to your function you need to declare the parameters As Variant and then check if they are null by using the IsNull() function.

For example, if I have the following function

Option Compare Database
Option Explicit

Public Function TimesTwo(SourceValue As Long) As Long
    TimesTwo = SourceValue * 2
End Function

and I use it in a query where the source column can have null values

SELECT thing, TimesTwo(thing) AS thing2 FROM table1

then the rows with null values will return #Error

thing  thing2
-----  ------
    1       2
    2       4
       #Error
    4       8

However, if I change the function to

Option Compare Database
Option Explicit

Public Function TimesTwo(SourceValue As Variant) As Variant
    If IsNull(SourceValue) Then
        TimesTwo = Null
    Else
        TimesTwo = SourceValue * 2
    End If
End Function

then I get

thing  thing2
-----  ------
    1       2
    2       4

    4       8

Upvotes: 1

Related Questions