Conditional Function

[khwaja]

I am trying to construct a function but it seems little complicated. My attempt so far is as under. I have explained what I am trying to achieve. I guess it is only the last part is where I am stuck.

Public Function PNPReturnReqDateTest(strDivision As String, strDevCode As String, dteSiteStart1 As Variant, dteSFStart As Variant, dteBuilderComp As Variant) As Variant

On Error GoTo Err_PNPReturnReqDateTest

'OrderByDateTest: If no relevant dates are available, then return no date;

If IsNull(dteSiteStart1) And IsNull(dteBuilderComp) Then
PNPReturnReqDateTest = Null

'OrderByDateTest: If there is a shopfitting date, then give me a date 182 days less than the shopfitting date.

ElseIf strDivision = "Supermarket" And Not IsNull(dteSFStart) Then
PNPReturnReqDateTest = DateAdd("ww", -182, dteSFStart)

'OrderByDateTest: Where there is no shop fitting date available for a brand new store, give me a date 238 days less than the buildercomp date.

ElseIf strDivision = "Supermarket" And strDevCode Like "*N*" And IsNull(dteSFStart) Then
PNPReturnReqDate = DateAdd("ww", -238, dteBuilderComp)

'OrderByDateTest: If there is no shopfitting date and it is not a new store, then check if the (opening date less 182 days) is less than the sitestart date and if true, sitestart less 182 days otherwise buildercomp less 364 days.


ElseIf strDivision Like "*Supermarket*" And (strDevCode not Like "*N*") Then
PNPReturnReqDate = DateAdd("d", -182, dteBuilderComp) < dteSiteStart1 …...

End If

Exit_PNPReturnReqDateTest:
Exit Function
Err_PNPReturnReqDateTest:
MsgBox "Error " & err.Number & " " & err.Description
Resume Exit_PNPReturnReqDateTest
End Function

 

[Privateer]

khwaja,
A few general observations. Before you call a function, check if the fields are populated. This way you can avoid using variants and you can send the value as a valid date or string. You are sending five values to a function, a bit too much. And you are sending back a null, which is really bad. With a function you always want to send back some value. Again, check those two values (dteSiteStart1 & dteBuilderComp) before you call the function.

If your table is normalized properly, strDivision should be a number identifying it as a supermarket, and strDevCode should also be a number. You rarely ever check text. In my tables, even for something simple like gender, I store -1 for Male and 0 for Female, never the words.

Another issue is you have a lot of nested IF statements all strung together; five or six is about the max and then I get a headache and Access says get lost. You can't really tell which date has been calculated and whether the calculatation is continuing after a valid answer has been determined or which else is being used. Your logic is hard to follow here.

Try coding it this way, assuming all fields were checked for nulls and valid dates before the function was called. Also you said days, so I changed the "ww" (weeks) to "d" for days.

Dim TempDate as Date

If strDivision = "Supermarket" Then
IF IsDate(dteSFStart) = true Then
TempDate = DateAdd("d", -182, dteSFStart)
ELSE 'dteSFStart is empty or not a date
If strDevCode Like "*N*" Then
TempDate = DateAdd("d", -238, dteBuilderComp)
ELSE 'the strDevCode does not have an "n" in it and dteSFStart is not a date
TempDate = DateAdd("d", -182, dteBuilderComp)
END IF
END IF
TempDate = Date 'send back todays date or something because strDivision does not equal "Supermarket"
END IF
PNPReturnReqDateTest = TempDate

There might be one more level of logic, but it is late at night and I think you get the idea. You may want to break this up into two functions one where the dtsSFStart is a date and one where it is empty. And remember the command GOTO Exit_PNPReturnReqDateTest. When you have the result you want, get out of the function, or make sure another IF statement doesn't get executed. You don't want the answer to get over written later on and return a wrong answer. Hope this helps.

Privateer

 

[khwaja]

Thanks a lot for your help. I learnt a lot from this and will endeavour to work along these lines. Many thanks.

 

[khwaja]

Sorry to be back. I have modified the code a little to suit my requirements but I am constantly coming with 'invalid use of null error. Could you kindly see if this can be fixed.

Dim Tempdate1 As Date
Dim Tempdate2 As Date

If strDivision = "Supermarket" Then
If IsDate(dteSFStart) = True Then

Tempdate1 = DateAdd("d", -182, dteSiteStart1)

Else

Tempdate2 = DateAdd("d", -182, dteBuilderComp)

End If
End If

If (strDevCode Like "*E*" Or strDevCode Like "*R*") And Tempdate2 < Tempdate1 Then

PNPReturnReqDateTest = DateAdd("d", -182, dteSiteStart1)

Else

PNPReturnReqDateTest = DateAdd("d", -182, dteSFStart)

End If

 

[vbaInet]

It can be fixed if you tell us what code line it highlights when you click the Debug button.

 

[khwaja]

Thanks. Apologise for the oversight. It is the following line where it stops with run time error 94, invalid use of Null.

Tempdate2 = DateAdd("d", -182, dteBuilderComp)

 

[vbaInet]

Perform a Null check using the IsNull() function before assigning it to the Tempdate2 date variable. Assigning Null to a date variable, such as Temdate2 will cause it to error.