Call Function within Query

[RENENGER]

I am trying to calculate a Work Order Start Date in a query. This is based on some characteristics of the order and it skips weekends. However, when I run this query, the date shows as 12:00 am. What am I missing?

Function CreateWOSDDate(ByVal PreFin As String, ByVal DrStyle As String, ByVal Deldate As Date) As Variant

Dim AddColor As Boolean
Dim intNumDays As Integer
Dim StartDate As Date


Select Case PreFin

Case "BR17", "BR28", "WH06"
AddColor = True
Case Else
AddColor = False
End Select

Select Case DrStyle

Case "DCREag", "DCRHWK", "DCRFAL", "RP-9", "RP-22", "RP-23"
If AddColor Then
intNumDays = 7
Else
intNumDays = 6
End If

Case Else
If AddColor Then
intNumDays = 7
Else
intNumDays = 4
End If
End Select

StartDate = MinusWorkdays(Deldate, intNumDays)


End Function

 

[Golom]

I don't know exactly what MinusWorkDays does but the more critical problem is that you are not setting CreateWOSDDate so the function isn't returning anything.

 

[RENENGER]

What am I doing incorrectly? I am still learning this VB stuff.

 

[RENENGER]

Ok. It looks like I got it to work. However, if the query runs that actually contains the CASE statements in the Function (Pre-Fin is BR28 or DrStyle = DCREAG) then I get an error that the expression is typed incorrectly, or it is too comple to be evaluated. I had to change the Function around a little bit.

Function CreateWOSDDate(ByVal PreFin As String, ByVal DrStyle As String, ByVal Deldate As Date) As Date

Dim AddColor As Boolean
Dim intNumDays As Integer



Select Case PreFin

Case "BR17", "BR28", "WH06"
AddColor = True
Case Else
AddColor = False
End Select

Select Case DrStyle

Case "DCREag", "DCRHWK", "DCRFAL", "RP-9", "RP-22", "RP-23"
If AddColor Then
intNumDays = 7
Else
intNumDays = 6
End If

Case Else
If AddColor Then
intNumDays = 7
Else
intNumDays = 4
End If
End Select

CreateWOSDDate = MinusWorkdays(Deldate, intNumDays)


End Function

SELECT tblFrontierUnits.Deldate, tblMainFrontierUnits.ProjectID, tblMainFrontierUnits.Phase, tblMainFrontierUnits.Unit, tblMainFrontierUnits.Tract, tblMainFrontierUnits.Release, tblMainFrontierUnits.UnitPlan, tblFrontierUnits.UnitOpt, Sum(tblFrontierUnits.Boxes) AS SumOfBoxes, tblFrontierUnits.Species, tblFrontierUnits.DrStyle, tblFrontierUnits.PreFin, CreateWOSDDate([Prefin],[DrStyle],[DelDate]) AS StartDate
FROM tblMainFrontierUnits LEFT JOIN tblFrontierUnits ON (tblMainFrontierUnits.UnitPlan = tblFrontierUnits.UnitPlan) AND (tblMainFrontierUnits.Release = tblFrontierUnits.Release) AND (tblMainFrontierUnits.Tract = tblFrontierUnits.Tract) AND (tblMainFrontierUnits.Unit = tblFrontierUnits.Unit) AND (tblMainFrontierUnits.Phase = tblFrontierUnits.Phase) AND (tblMainFrontierUnits.ProjectID = tblFrontierUnits.ProjectID)
GROUP BY tblFrontierUnits.Deldate, tblMainFrontierUnits.ProjectID, tblMainFrontierUnits.Phase, tblMainFrontierUnits.Unit, tblMainFrontierUnits.Tract, tblMainFrontierUnits.Release, tblMainFrontierUnits.UnitPlan, tblFrontierUnits.UnitOpt, tblFrontierUnits.Species, tblFrontierUnits.DrStyle, tblFrontierUnits.PreFin, CreateWOSDDate([Prefin],[DrStyle],[DelDate])
HAVING (((tblFrontierUnits.Deldate) Between [Enter Starting Date:] And [Enter Ending Date:]));