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
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...
But have no idea how to assign the dates from the table to the placeholders StartDate and EndDate
Thanks
Ronan
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
Code:
=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...
Code:
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
Last edited: