Developing a complex module

[Alex Motilal]

Hi everybody,
I have developed a Database for Ginning of cotton. The cotton seed generated is sold. When the seed is ready, it is intimated to the buyer and he makes payment according to the contracted rate. The buyer has to lift the seed within 5 days of making the payment. If he exceeds 5 days, 1%(for 30 days) late lifting charges is levied on the seed value. If any weekends (Saturdays & Sundays)or national holidays intervene, they are excluded. For example if the difference is 10 days and 2 weekends & 1 holiday intervene, calculation is done for 7 days.
The database has the following Table & Fields for entering holidays:
tblDeclaredHolidays


Fields: DecHolidayDate (Date), DecHolidayReason (Text)


To evaluate the Holidays falling between the dates I am using the following Function given by this Forum in a Module:
Option Compare Database
Option Explicit
Public Function InterveningHolidays(StipulatedDate As Date, DeliveryDate As Date) As Integer
Dim rst As DAO.Recordset
Dim strSQL As String
' set default return value
InterveningHolidays = 0
strSQL = "SELECT Count([DecHolidayReason]) AS Holidays " & _
"FROM tblDeclaredHolidays " & _
"WHERE [DecHolidayDate] BETWEEN #" & StipulatedDate & "# AND #" & DeliveryDate & "#"
' open recordset
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
InterveningHolidays = rst("Holidays")
' clean up
rst.Close
Set rst = Nothing
End Function


To Evalute the intervening Weekends, I am using the following Function in a separate module again given by this Forum:
Option Compare Database
Public Function CountWeekendDays2(dtStart As Date, dtEnd As Date) As Integer
Dim AcDtStart As Date
Dim intSat As Integer
Dim intSun As Integer
AcDtStart = IIf(dtStart > dtEnd, dtEnd, dtStart)
'This function assumes dtStart <= dtEnd
CountWeekendDays2 = 0
intSat = DateDiff("d", GEDay(AcDtStart, 7), LEDay(dtEnd, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(AcDtStart, 1), LEDay(dtEnd, 1)) / 7 + 1
CountWeekendDays2 = Ramp(intSat) + Ramp(intSun)
End Function
Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + Weekday(dtX) - vbDay) Mod 7, dtX)
End Function
Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - Weekday(dtX)) Mod 7, dtX)
End Function
Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function

The above Functions calculate the intervening Holidays & Weekends in a Query. The fields in the Query are:
SeedValue, DtOfPayment, DtOfDelivery (Fields from corresponding tables)
Syntax for calculating the Late Lifting Days is as follows:
LateLiftingDays:IIf(([DtOfDelivery]-[DtOfPayment])<4,0,([DtOfDelivery]-[DtOfPayment])-(InterveningHolidays([DtOfPayment],[DtOfDelivery])+(CountWeekendDays2([DtOfPayment],[ DtOfDelivery])))


Syntax for calculating the Late Lifting Charges is as follows:
LLCharges: Round((([SeedValue]*(1/100))/30)*[LateLiftingDays],0)

The above modules and syntaxes give the correct results. However, I want to know if it is possible to create a single module combining the both the modules and the syntaxes. If so I request the experts to please construct a single module for me.
With lots of thanks,
Alex

[ssanfu]

The above modules and syntaxes give the correct results. However, I want to know if it is possible to create a single module combining the both the modules and the syntaxes.

I'm not really sure what you want, mainly because of terminology.

A module is a container. It holds the subroutine (Sub) & function code.

You provided 5 functions. Do you want to put all of the functions into one module (container)? If so, cut the functions out and paste them into a module.

Or do you want to combine the function that calculates the number of holidays with the function that calculates the number of weekend days into one function?

What have you tried?

[Alex Motilal]

Dear ssanfu,

Thank u for the reply. You only gave me the two functions to one of my earlier threads.

I want to combine the function that calculates the number of holidays with the function that calculates the number of weekend days as well as the syntax that calculates the late lifting charges into one function.

Alex

[Alex Motilal]

Dear ssanfu,
With your tips I succeeded in combining the functions. This is what I did:

Option Compare Database
Option Explicit
Public Function InterveningTotalHolidays(IntimationDt As Date, PaymentDt As Date, DeliveryDate As Date) As Integer
Dim InterHolidays As Integer
Dim CountWEdays As Integer
Dim rst As DAO.Recordset
Dim strSQL As String

' set default return value
InterHolidays = 0
strSQL = "SELECT Count([DecHolidayReason]) AS Holidays " & _
"FROM tblDeclaredHolidays " & _
"WHERE [DecHolidayDate] BETWEEN #" & StipulatedDt(IntimationDt, PaymentDt) & "# AND #" & DeliveryDate & "#"
' open recordset
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
InterHolidays = rst("Holidays")
' clean up
rst.Close
Set rst = Nothing

Dim AcDtStart As Date
Dim intSat As Integer
Dim intSun As Integer
AcDtStart = IIf(StipulatedDt(IntimationDt, PaymentDt) > DeliveryDate, DeliveryDate, StipulatedDt(IntimationDt, PaymentDt))
'This function assumes StipulatedDate <= DeliveryDate
CountWEdays = 0
intSat = DateDiff("d", GEEDay(AcDtStart, 7), LEEDay(DeliveryDate, 7)) / 7 + 1
intSun = DateDiff("d", GEEDay(AcDtStart, 1), LEEDay(DeliveryDate, 1)) / 7 + 1
CountWEdays = Raamp(intSat) + Raamp(intSun)
InterveningTotalHolidays = InterHolidays + CountWEdays
End Function
Public Function LEEDay(DtX As Date, vbDay As Integer) As Date
LEEDay = DateAdd("d", -(7 + Weekday(DtX) - vbDay) Mod 7, DtX)
End Function
Public Function GEEDay(DtX As Date, vbDay As Integer) As Date
GEEDay = DateAdd("d", (7 + vbDay - Weekday(DtX)) Mod 7, DtX)
End Function
Public Function Raamp(varX As Variant) As Variant
Raamp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
Public Function StipulatedDt(DtI As Date, DtP As Date) As Date
StipulatedDt = IIf((DtI >= DtP), (DtI + 4), (DtP + 4))
End Function
Public Function LateLiftingCharges(IntmDt As Date, PmtDt As Date, DelDt As Date, SdValue As Double) As Double
Dim LLratePerCent As Single
LLratePerCent = DLookup("[LLcharges%]", "tblCCrates", "[DateFrom]<= #" & StipulatedDt(IntmDt, PmtDt + 4) & "# And Nz([DateTo],#12/31/9999#)> #" & StipulatedDt(IntmDt, PmtDt + 4) & "#")
LateLiftingCharges = Round((([SdValue] * (LLratePerCent / 100)) / 30) * LLdays(IntmDt, PmtDt, DelDt), 0)
End Function
Public Function LLdays(IntiDt As Date, PymtDt As Date, DeliDt As Date) As Integer
LLdays = IIf((DeliDt - StipulatedDt(IntiDt, PymtDt)) - InterveningTotalHolidays(IntiDt, PymtDt, DeliDt) < 0, 0, (DeliDt - StipulatedDt(IntiDt, PymtDt)) - InterveningTotalHolidays(IntiDt, PymtDt, DeliDt))
End Function

Thanks a lot

[ssanfu]

Good job.

To make it easier to read the code, you might (I do) put a blank line between functions....