DateAdd Weirdness (1 Viewer)

[hi there]

Howdy all,

I've written a simple little function using the DateAdd system function which i call from query. Here's the logic

If (PassFail = "Pass") Then
IsNull (FirstRepairDueDate)
Else
FirstRepairDueDate = DateAdd("d", 5, InspectionDate)
End If

basically if the PassFail argument is "Pass" I would like the function to not display anything, however i'm not sure what to do because my function returns a data type of Date, which it defaults to 12:00:00 AM using the above logic.

Any ideas?

Many thanks

 

[EMP]

If (PassFail = "Pass") Then
FirstRepairDueDate = Null
Else
FirstRepairDueDate = DateAdd("d", 5, InspectionDate)
End If

^

 

[hi there]

Hi EMP,

Thanks for the response. I tried assigning the variable to Null, however this didn't work. I got a "invalid use of Null" which i think makes sense because Null doesn't really equal anything.

Any ideas?

 

[neileg]

Where are you executing this code? I presume it's in a form (though this is the Queries forum...)

You should be using the names of the controls on the form not the field names.

 

[EMP]

Or if FirstRepairDueDate is a variable in VBA, it must be declared as a variant, that is

Dim FirstRepairDueDate As Variant

not Dim FirstRepairDueDate As Date. Only a variant variable can store a Null value.

^

 

[hi there]

Hi EMP and neileg,

EMP is correct i'm using this code as a public function (not on form although perhaps this belongs under the VBA forum) that i call from various queries throughout my application. i tried changing FirstRepairDueDate to a Variant, but this didn't work. I think because my function itself returns a Date data type. When i changed my function to return a Variant data type everything worked.

Many thanks for the help.

Have a nice day.