Query Formulae as VB code

[pbaldy]

No problemo; post back if you get stuck.

 

[khwaja]

Hello Paul, If i could bother you a little bit more. I managed to get the function working well. However, I am still getting errors especially in those cases where all three dates are not there. Need litlle your help in removing these errors. I have attched the tem db for your perusal. Cheers

 

[pbaldy]

Well, you have code in your function to handle Nulls, but the first line itself is the problem:

Public Function ReturnReqDate(strDivision As String, strDevCode As String, dteSiteStart1 As Date, dteTradersComp As Date, dteBuilderComp As Date) As Date

A variable declared as "Date" can not accept a Null. The only data type that can accept the Null is Variant, so change any type that might be Null to Variant (including the return value, since you set it to Null).

 

[khwaja]

Thankls Paul. I already tried that too but there was not no ce except for the fact that values were reported as text. But errors remained the same.

 

[pbaldy]

What exactly did you try? I changed them in your db, and the errors were gone.

 

[khwaja]

Thanks. I merely changed the first line to:
Public Function ReturnReqDate(strDivision As String, strDevCode As String, dteSiteStart1 As Date, dteTradersComp As Date, dteBuilderComp As Date) As Variant

 

[pbaldy]

Well, I said to "change any type that might be Null to Variant (including the return value...". You changed the return value, but none of the others:

Public Function ReturnReqDate(strDivision As String, strDevCode As String, dteSiteStart1 As Date, dteTradersComp As Date, dteBuilderComp As Date) As Variant

 

[khwaja]

Thanks. I changed it to the following but then the code excution stops at 'Null' invalid use of Null. Run time error 94.

Public Function ReturnReqDate(strDivision As String, strDevCode As String, dteSiteStart1 As Variant, dteTradersComp As Variant, dteBuilderComp As Variant) As Date

On Error GoTo Err_ReturnReqDate

If IsNull(dteSiteStart1) And IsNull(dteTradersComp) And IsNull(dteBuilderComp) Then
ReturnReqDate = Null

 

[pbaldy]

Public Function ReturnReqDate(strDivision As String, strDevCode As String, dteSiteStart1 As Variant, dteTradersComp As Variant, dteBuilderComp As Variant) As Variant

 

[khwaja]

Many thanks for your kind help.