Created a function Cannot get it to work (1 Viewer)

[wan2fly99]

I have created a function to check for three dates passed in Which ever one is not null return that date
I call the function from a form for an unbound field I set the control source of the unbound field to call this function

Anyone of the dates can be null Can't I not pass down a null field?

=returnDate([Date1],[Date2],[Date3])

it gives me an error

Function:

Public Function returnDate(Eff As Date, Pro As Date)

If Not IsNull(Eff) Then
returnDate = Eff
Else
If Not IsNull(Pro) Then
returnDate = Pro
Else
returnDate = #1/1/2999#
End If
End If

End Function

 

[DJkarl]

I have created a function to check for three dates passed in Which ever one is not null return that date
I call the function from a form for an unbound field I set the control source of the unbound field to call this function

Anyone of the dates can be null Can't I not pass down a null field?

=returnDate([Date1],[Date2],[Date3])

it gives me an error

Function:

Public Function returnDate(Eff As Date, Pro As Date)

If Not IsNull(Eff) Then
returnDate = Eff
Else
If Not IsNull(Pro) Then
returnDate = Pro
Else
returnDate = #1/1/2999#
End If
End If

End Function

The function you are calling has 3 arguments but the one you've posted only has two, this will cause an error. You could use optional arguments if the number of dates passed will change.

If the dates are being fed from code then they won't be null, a Date is a primitive type that is initialized to 12:00:00 AM by default, if the Date is being passed from a form or table it could be null but you will likely get an error trying to populate a date variable with a null value.

 

[namliam]

Public Function returnDate(Eff As Date, Pro As Date)

Your function only allows for 2 dates to be input, not 3..

 

[wan2fly99]

I actually pass three dates and the dates are from a table which can contain null

When I pass a date that is null it doesn't work
This is what I have


=returnDate([Date1],[Date2],[Date3],[Date3]) is on the control source of the unbound field in a form

it gives me an error

Function:

Public Function returnDate(Eff As Date, Pro As Date, Exp as Date)

If Not IsNull(Eff) Then
returnDate = Eff
Else
If Not IsNull(Pro) Then
returnDate = Pro
Else
returnDate = #1/1/2999#
End If
End If

End Function

 

[namliam]

Now you send in 4 where the function allows for only 3 ??

 

[wan2fly99]

type error

=returnDate([Date1],[Date2],[Date3])

It gives me an error
Any of these passing fields can be null It seems the function will not accept them

 

[boblarson]

You can't pass a null field to a variable defined as Date. You CAN use a variant, or you would need to use NZ in the query to pass a 0 to the date field and then check to see if any had a value of 0.