Counting Days

[dazstarr]

Hi

Can someone help me with this please?

On a form I have a combo box which has 5 options - Mon - Fri

I have a date started field on the form too.

What I would like is to query how many of the given days fall between a date range.

For example a user selects Monday (it has to be weekday as it is for a taxi schedule), when they create the invoice to send to the taxi company, they open an invoice form and enter the dates from and to.

I would like to count how many Mondays fall between the dates from and to.

Can someone tell me if and how this is possible?

Many Thanks in advance
Daz

 

[RuralGuy]

You should find this link helpful.

 

[dazstarr]

Thanks Rural Guy - thats exactly what I am looking for. It's the top option I need.

I will give it a try and post back if it works.

Thanks again for your help.
Daz

 

[dazstarr]

Hi

I am struggling to implement this - I has basic programming skills.

So far I have copied the top part of it into a module:

/code
Public Function HowManyWD(FromDate As Date, _
ToDate As Date, _
WD As Long)
' No error handling actually supplied
HowManyWD = DateDiff("ww", FromDate, ToDate, WD) _
- Int(WD = WeekDay(FromDate))
End Function
\code

How would I then call the function: HowManyWD.

Please tell me if I have got this completely wrong!

Thanks in advance.
Daz

 

[RuralGuy]

That site is *loaded* with good advice and code.

 

[RuralGuy]

How would I then call the function: HowManyWD.

Have you tried playing with this function in the immediate window? You did put the function in a standard module, right?

 

[dazstarr]

Well what I did was copy the top bit of the code from the link you sent me, and created a new module and saved it as HowManyWD.

I then opened a query window and dragged the DateFrom & DateTo fields - im not really sure what to do next.

I assumed I call the HowManyWD function within the query but not really sure how.

 

[dazstarr]

I just figured out where the immediate window is - sorry I have never used this before and not really sure what it does.

 

[RuralGuy]

Do *not* name the module the same as the function! Name it basFunctions or something. Do you know how to open the immediate window? ^G

 

[dazstarr]

Thanks for replying.

Ok - I have renamed the module in Access.

I have opened the module and got the VB window up. I have clicked on on View - Immediate Window and a blank box has appeared below the code window. Not sure what to do with it.

Sorry for being a bit of a noob with coding.

Thanks again.

 

[RuralGuy]

In the immediate window type:
?HowManyWD(#10/01/2008#,#10/30/2008#,2)
and see what happens.

 

[dazstarr]

I pressed Enter after it and it came back with "4"

I take it that 4 is the number of days - and the 2 is a Monday (stab in the dark as 2 being the 2nd day of the week?)

So if I was correct - there are 4 Mondays between those dates?

 

[RuralGuy]

If you look up DateDiff in VBA help, there is a pretty good explaination on what is going on in the function. As for how it is used in a query you replace the dates with your [FieldNames] and leave the 2 for WD and see what happens in the datasheet view of the query builder.

 

[dazstarr]

Excellent!

Thanks so much for your help. I will give it a try this afternoon!

I really appreciate your expertise.

Many Thanks again
Daz

 

[RuralGuy]

Post back here if you need further assistance.

 

[dazstarr]

I tested it and replaced the dates with fields from my query and it works great!

Many Thanks for your help!

Daz

 

[RuralGuy]

Glad to have been of assistance.

 

[alihan1988]

You should find this link helpful.

thank you RuralGuy. Very useful.

 

[RuralGuy]

thank you RuralGuy. Very useful.

You're welcome. It is a great site.