Creating a Module to Calculate billing Days

[jasn_78]

Hi

I am currently writing a database to calculate customer's bills for a newspaper delivery service and I have two problems which I feel probably could be solved in the same module.

This is that I need to calculate a different price for saturdays then weekdays and also not inlcude any sundays for the billing period.

At the moment I am just doing this through a query where I have typed in each date of every Sunday and Saturday and in seperate tables and wrote a query to select and calculate the information that way. I was hoping some1 might be able to give me some guidance in how to create a module to do this as I have no experience in VB

Thanks

 

[SteveA]

Just a couple of questions:

Do you have a start and end date for the billing period? and,
Do you have a list of each day a newspaper was delivered for a particular customer or is it assumed that the paper is delivered every weekday and Saturday during the billing period?

Cheers,
SteveA

 

[raskew]

You can use this simple function as a guide.

Function AddEmUp(pstartdate As Date, penddate As Date, pdayrate As Currency, psatrate As Currency)
'Purpose: Provide total of daily costs, based on separate costs for weekdays
' and Saturdays, and excluding Sundays.
'To call: (From debug window) ? addemup(#10/1/01#, #10/31/01#, 0.50, 0.75)
'Returns: 14.5

Dim thetotal As Currency
Dim n As Integer

thetotal = 0
Do While pstartdate <= penddate
n = WeekDay(pstartdate)
'if the weekday is Saturday, use the Saturday rate, if it's Sunday,
'use 0, otherwise use the Daily Rate (pdayrate)
thetotal = thetotal + Switch(n = 7, psatrate, n = 1, 0, True, pdayrate)
pstartdate = pstartdate + 1
Loop
AddEmUp = thetotal
End Function

[This message has been edited by raskew (edited 10-20-2001).]

 

[jasn_78]

I have a given start date and end date for each billing period which is entered through a form.

As for what days papers are received I want to be able to create a module that calculates number of days stopped in a period for each customer if they ask for their paper to stop being delivered for a certain period. e/g being if a customer asks for a stop date of 21-10 and start again on the 31-10 i want to know how many actual papers they will not receive.

 

[Jack Cowley]

This should work for you:

Function MissingPapers(BegDate As Variant, EndDate As Variant) As Integer

' Note that this function does NOT account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0

Do While DateCnt <= EndDate

If Format(DateCnt, "ddd") <> "Sun" Then
EndDays = EndDays + 1
End If

DateCnt = DateAdd("d", 1, DateCnt)
Loop
MissingPapers= EndDays

End Function

[This message has been edited by Jack Cowley (edited 10-21-2001).]

 

[raskew]

Hopefully you understand that you are changing the playing field as you progress. The problems you wished to address in your first posting were not the same issues you described in your second posting.

Please get it together, decide what it is you want to do, and post back with an accurate description.

 

[jasn_78]

Rasekew:

I do fully understand they are two seperate issues, however if i create a module to calculate days the paper is received in a given period surely i can also calculate the days the paper is not received for with only having to change a few fields.

 

[raskew]

Great-

Where are you in this process? You've been provided with at least two workable solutions. Please show us the code you've developed based on the advice already provided.

[This message has been edited by raskew (edited 10-22-2001).]

 

[Travis]

Public Function NumberOfPapers(ByVal BegDate As Date, ByVal EndDate As Date) As String
Dim dDate As Date
Dim iSaturday As Integer
Dim iRegular As Integer

For dDate = BegDate To EndDate
If Weekday(dDate) = vbSaturday Then
iSaturday = iSaturday + 1
ElseIf Weekday(dDate) = vbSunday Then
'Add nothing no sunday paper
Else
iRegular = iRegular + 1
End If
Next dDate
NumberOfPapers = (iRegular + iSaturday) & " Total Papers," & iRegular & " Regular(s)," & iSaturday & " Saturday(s)"

End Function

Call it Like this:
NumberOfPapers(#10/15/2001#,#10/28/2001#)

Return Value:
12 Total Papers,10 Regular(s),2 Saturday(s)

You can then use the Split Command to turn this into an array. Or Simply returning the VAL of the Returned string will give you the total amount of papers for the Date Range. Depending on what the Range is for (Delivered/Not Delivered).

 

[jasn_78]

Raskew:

Thanks I am still in developing queries and forms for other parts of the database I just knew that this is something I would have problems with as I have done no V.B or really any sorts of programming before.

So thanks for your help and this is meant for every1 who has given me advice on this