Purpose built function needed to count days in a period (1 Viewer)

[Ronanm]

Hi

I have a large piece of data which I "was" minipulating in Excel, using complex formulas and some vba, but as I know will be looking at various reports, I have imported them to Acess as tables... And was trying to query the table na dpull the number of days.

Unfortunately I am struggling trying to use an equivilant "long" formula that I would have used in Excel... see below

=IF(AH33=0.5,0.5,IF(AND(AC33>=$J$1,AC33<=$K$1,AB33<=$K$1),NETWORKDAYS(AC33,AB33),IF(AND(AC33>=$J$1,AC33<=$K$1,AB33>$K$1),NETWORKDAYS(AC33,$K$1),IF(AND(AC33>=$J$1,AB33>=$J$1,AB33<=$K$1),NETWORKDAYS($J$1,AB33),IF(AND(AC33<$J$1,AB33>$K$1),NETWORKDAYS($J$1,$K$1),IF(AND(AC33<$J$1,AB33>$J$1,AB33<$K$1),NETWORKDAYS($J$1,AB33,0)))))))

It basically counts network days that fall within a period (4 weeks), looking at the courses start and end dates. So in some cases, a course may be 5 days long, with 3 days falling in one period and 2 days in the next...

I'm guessing I can use a "Case" statement in a module, but struggle seeing how or where to add field names etc.....

Any pointers at all would be helpful...

The Code in a module I tried looked a little like below...

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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

But have no idea how to assign the dates from the table to the placeholders StartDate and EndDate

Thanks

Ronan

 

[Alansidman]

Take a look at this web site. It will show you how to use the =IIF function in a nested format. Note that in Access the function is IIF and not IF.

Alan

 

[Ronanm]

Take a look at this web site. It will show you how to use the =IIF function in a nested format. Note that in Access the function is IIF and not IF.

Alan

Thanks, I have a good idea regarding the IIF, it's just putting it into a module as a built in Function, I struggle with...

Cheers

 

[Alansidman]

Ok, then look at this at how to do it in VBA which is what you would use in your module.

http://www.techonthenet.com/access/functions/advanced/if_then.php

Alan