Counting Days Problem

[dazstarr]

Hi everyone

Can someone advise me what I can do to fix this problem please?

I have a query which calculates how many days between two date fields which works great. I use a module to do this and call the function in my query.

The database is for Taxi Scheduling. If a user selects a Friday (I have a Day combo box), it calculates how many Fridays are between 2 invoice dates. This works fine.

I am struggling with the second part of the query. I would like to count the number of Days between the 2 invoice fields but it has to take into account the EffectiveDate.

I have 3 fields:

EffectiveDate
InvoiceFrom
InvoiceTo


So If:
Day: Thursday
EffectiveDate = 25/09/08
InvoiceFrom = 23/09/08
InvoiceTo = 27/09/08

The answer should be 1 instance (of a Thursday between the two invoice dates)

Another example:

Day: Friday
Effective Date = 01/10/08
Invoice From = 01/11/08
InvoiceTo = 30/11/08

The answer should be 5 instances (of how many Fridays between the two invoice dates).

The problem I am having is that the query doesn’t even look at the EffectiveDate field and just counts how many days between the two invoice fields which gives me the wrong number of days as the EffectiveDate could be anywhere between the 2 invoice fields.

I hope I make sense.

Does anyone know how I can do this in a query please?

Many thanks in advance
Daz

 

[DCrake]

If I understand correctly the first thing that needs establishing is what day of the week is the effective date, irrespective of what date it is. Then once done loop though the given date range and count the number of instances that the day of the week occurs.

If correct then use the following

Public Function EffectiveDays(dtmEffective As Date, dtmStart As Date, dtmEnd As Date) As Integer
Dim DOW As String
Dim d As Integer
Dim Today As Date
Dim rCnt As Integer
DOW = Weekday(dtmEffective)
d = DateDiff("d", Format(dtmStart, "Medium Date"), Format(dtmEnd, "Medium Date"))


Today = dtmStart

For x = 0 To d
   If Weekday(Today) = DOW Then
       rCnt = rCnt + 1
  End If
    Today = DateAdd("d", 1, Today)
Next

EffectiveDays = rCnt
End Function

Psuedo code

What day of the week is the effective date
How many days are there between the start and date

Loop through each date checking if the week day matches the effective weekday

if so add one to the running sum

return the results of the running sum back to the function.

 

[dazstarr]

Many thanks for your reply.

I will give it a try now and postback.

Thanks again

 

[dazstarr]

Hi

I think the code you gave me would solve the problem but unfortunately - the Day of the week is manually selected from a combo box. (Mon, Tue, Wed, Thu, Fri).

A user would select which Taxi Firm and what their schedule is for the week:

From [EffectiveDate], every [Day] - go to X address.

The have to do it this way in order for the schedule to be correct.

This way - each Taxi would have a schedule what they are doing each week - Mon - Fri.

The effective date is when that particular journey is added. Unfortunately due to the setup - the effective date cannot be used to calculate the [Day] field.

At the moment – the query is counting how many eg Fridays [Day] fall between the two Invoice dates.

What I need the query to do is only calculate how many [Days] fall between the [InvoiceStart] and [InvoiceEnd] date fields. But if the effective date fell between those dates – this would need to be taken into account into the calculation.

Apologies if this doesn’t make much sense – I'm struggling getting the logic right in my own head – let alone on paper.

Thanks for your help.
Daz

 

[DCrake]

So from what you are saying is that you need to create a time line

This is either Start Date to End date if the effective date does not fall between these two date, however if the effective date does fall between these two date make the start date the effective date. so the new time line is the effective date to the end date.

Therefore the first thing you need to do is to ask if the effective date is after the start date and before the end date> If it is then the lower date becomes the effective date, otherwise use the start date as the lower date.Thus

LowerDate = IIF(EffectiveDate > StartDate,EffectiveDate,StartDate)
UpperDate = EndDate

Date Range = Lower date - upper date
Then calculate the number of Monday's, for example occur between the dates.

What you are trying to achieve, I think is that Ok we are going to invoice you for the whole of April but you only went to John Doe's house twice.

David

 

[dazstarr]

Exactly!!

Thank God for people who can decipher my nonsense!

I will give it a try and post back shortly.

Thanks for your help!!
Daz

 

[dazstarr]

Ok, I am struggling implementing this in my database. I am going to continue to try this afternoon - hopefully it won't drag on until the morning.

As soon as I have sorted this (or require further assistance!) I will post back.

Thanks for your help with this. Much appreciated.

Daz

 

[dazstarr]

All sorted!

Thanks David. I really appreicate your expertise.

I created the Upper & lower date fields in my query put the calculation in - and it works perfectly!

Many thanks!!

Daz