Days between

[mariaw]

Hi

I have a table which has a start date and an end date on it: e.g. 25/10/06 to 3/11/06.

I want to set up a query to calculate the number of days between those two dates, but only for the month of October: so it would only come to 7 days (not 10 days).

How do I do this please??

Also: once I have the formula worked out, how can I write it so that a user can enter the month they want to query (e.g. october)?

Thanks

Maria

 

[gemma-the-husky]

you might need to write your own function, but something like this macrocode
assuming fromdate is less than todate

function daysinmonth(fromdate, todate) as long

dim startdate as date
dim finishdate as date

error checks
if fromdate>31st October then 
     daysinmonth =0
     exit
end if
if todate<1st October then 
     daysinmonth =0
     exit
end if

startdate = max(fromdate, 1st October")
finishdate = min(todate, 31st October")

daysinmonth = finishdate-startdate

get the month from a textbox or variable, and set the control dates for first and last day of month by using datevalue or dateserial functions, whichever you find easier

 

[mariaw]

Hi Gemma

I am on Access 97 - do I put this code into an update query?

Maria

 

[gemma-the-husky]

its like the other one - you put this code (or something like it - this isnt finished) in a module, and then you can refer to the function in your query.

 

[mariaw]

...sorry...I have put the function code into a module called "Days between"; I have set up a new query (which includes the info that I want to query) - how do I refer to that function (e.g. that is in the module?)

M

 

[stopher]

Hi

I have a table which has a start date and an end date on it: e.g. 25/10/06 to 3/11/06.

I want to set up a query to calculate the number of days between those two dates, but only for the month of October: so it would only come to 7 days (not 10 days).

How do I do this please??

Also: once I have the formula worked out, how can I write it so that a user can enter the month they want to query (e.g. october)?

Thanks

Maria

You could use the following formula where stdate is your start date...

DateDiff("d",[stdate],DateSerial(Year(DateAdd("m",1,[stdate])),Month(DateAdd("m",1,[stdate])),1))

The above will work for any start month. It works by determining the date serial for the 1st of the next month which can then be used to find the number of days difference.

I'm not sure I understand the relevance of the end date in your example because it's not needed for the calculation unless end date may be in the same month ?

I can see where gemma is heading, in which case could it be that you have dates 20th Nov - 5th Jan and you want to calc days in December ? Need to know your ultimate requirements really.

Stopher

 

[mariaw]

Hi Stopher

Your last paragraph is correct: e.g. someone could be off ill from 20 Nov to 5th Jan, and I want to know (for a report on the section that that person works for) - how many days were lost in December for any employees in that section.

M

 

[stopher]

it's back to a function then...

Try this...

Public Function DaysInMonth(fromdate As Date, todate As Date, monthnum As Integer, yearnum As Integer) As Integer
 
'error check to see if month/year is in start/end date range
If fromdate >= DateSerial(yearnum, monthnum + 1, 1) Or todate < DateSerial(yearnum, monthnum, 1) Then
    DaysInMonth = -1
    Exit Function
End If


If Month(fromdate) = monthnum And Year(fromdate) = yearnum Then
'calc days in month where required month is same as startdate
    DaysInMonth = DateSerial(yearnum, monthnum + 1, 1) - fromdate
    
ElseIf Month(todate) = monthnum And Year(todate) = yearnum Then
'calc days in month where required month is same as finishdate
    DaysInMonth = todate - DateSerial(yearnum, monthnum, 1) + 1
Else
'calc days in month where required month is and in between month
    DaysInMonth = DateSerial(yearnum, monthnum + 1, 1) - DateSerial(yearnum, monthnum, 1)
End If


End Function

You'll need to call the function like this:
DaysInMonth([startdate],[enddate],chosenmonthnumber, chosenyearnumber)
Note -1 is returned if the month/year is outside your date range

I haven't really tested this

hth
Stopher

 

[mariaw]

Hi Stopher

OK I have put the function into a module....where do I put the fields in that I want it to analyse?

thanks

m

 

[stopher]

Hi Stopher

OK I have put the function into a module....where do I put the fields in that I want it to analyse?

thanks

m

Take a look at my example. Look at Query1 which shows the formula being used for different months.

When putting some data in I noticed that the function doesn't work for the case when the start and end date are in the same month but I guess you might be able to fix that by extending the function. If not, let me know.

hth
Stopher