Query using a User defined function for dates

[RonanM]

Hi

I have "attempted" to create a user defined function, to use to workout the actual days the fall within a Period. This is to save me needing to use a complex nested if statment in the Sql

The function compiles (big deal), but it is not what I want.

Code below:

Option Compare Database
Option Explicit
Public Function WorkingDaysInPeriod(StartDate As Date, EndDate As Date) As Integer
'On Error GoTo Err_WorkingDaysInPeriod
Dim intCount2 As Integer
Dim Ans1 As Date
Dim Ans2 As Date
Ans1 = InputBox("Enter Period Start Date")
Ans2 = InputBox("Enter Period End Date")
Ans1 = DateValue(Ans1)
Ans2 = DateValue(Ans2)
Select Case WorkingDaysInPeriod(StartDate, EndDate)
Case StartDate >= Ans1 _
And StartDate <= Ans2 _
And EndDate <= Ans2
intCount2 = WorkingDays(StartDate, EndDate) + 1
Case StartDate >= Ans1 _
And StartDate <= Ans2 _
And EndDate > Ans2
intCount2 = WorkingDays(StartDate, Ans2) + 1
Case StartDate >= Ans1 _
And EndDate >= Ans1 _
And EndDate <= Ans2
intCount2 = WorkingDays(Ans1, EndDate) + 1
Case StartDate < Ans1 _
And EndDate > Ans2
intCount2 = WorkingDays(Ans1, Ans2) + 1
Case StartDate < Ans1 _
And EndDate > Ans1 _
And EndDate < Ans2
intCount2 = WorkingDays(Ans1, EndDate) + 1
Exit_WorkingDayInPeriods:
Exit Function
End Select
'Err_WorkingDaysInPeriod:
'Select Case Err
'
'Case Else
'MsgBox Err.Description
'Resume Exit_WorkingDaysInPeriod
'
'End Select
End Function

And this was the nested IIF staement in the sQL:

IIF(BD.[Class Start Date]>=[Please Enter Period Start Date] AND BD.[Class Start Date]<=[Please Enter Period End Date] AND BD.[Class End Date]>[Please Enter Period End Date],WORKINGDAYS(BD.[Class Start Date],[Please Enter Period End Date])+1,IIF(BD.[Class Start Date]>=[Please Enter Period Start Date] AND BD.[Class End Date]>=[Please Enter Period Start Date] AND BD.[Class End Date]<=[Please Enter Period End Date],WORKINGDAYS([Please Enter Period Start Date],BD.[Class End Date])+1,IIF(BD.[Class Start Date]<[Please Enter Period Start Date] AND BD.[Class End Date]>[Please Enter Period End Date],WORKINGDAYS([Please Enter Period Start Date],[Please Enter Period End Date])+1,IIF(BD.[Class Start Date]<[Please Enter Period Start Date] AND BD.[Class End Date]>[Please Enter Period Start Date] AND BD.[Class End Date]<[Please Enter Period End Date],WORKINGDAYS([Please Enter Period Start Date],BD.[Class End Date])+1,0)))))) AS Calculation

If that makes sense to anybody, any assistance would be much appreciated...

Thanks

Ronan

[June7]

You are calling the function within itself on the Select Case line. This is recursive, that what you want? What is the function WorkingDays? Call the function in the VBA immediate window and step debug, follow the code as it executes.

[RonanM]

Nope, recursive isn't what I wanted, so I'll move that, and see what happens. I wondered why it kept prompting for the input box....

Really, what I was attempting was to create the function so that it would look at the start and end dates in the a table, then use the parameters Enter start date and End date, to work out the days that fall within the period...

The WorkingDays Function just counts the days Monday to Friday. And that works fine.

Cheers

Ronan

[June7]

It's late and I can't see straight but Select Case might not be right here, maybe needs to be IF THEN ELSEIF.

[RonanM]

No worries... 11am here.... I'll have a play anyway. Cheers...