Results 1 to 11 of 11
  1. #1
    Join Date
    May 2011
    Posts
    6

    Unanswered: Difference between dates - not as simple as I'd hoped!

    Hi

    I'm newly registerd on this forum but have lurked for a while and picked up many tips, so thanks for those! However cant find anything to help with this one:

    I have two date fields:
    [STARTDATE] and [ENDDATE] (both in format dd/mm/yyyy) providing the dates someone arrives (start) and the departure date (end).

    Each person is only allowed to stay a permitted number of days each year:
    1/April - 31/March.

    I want to be able to work out the number of days between the two dates then take this from the total they are allowed (which I can do).

    The problem lies when the dates span the two financial years ie starts before 31 March and ends after 1 April.

    If they stay from 28/March - 05/April. I'd like the calculation to only include from the 01/April-05/April for that year and the 28/March-31/March to be included in the previous years total.

    Im presuming it'll be some sort of IF statement? But not sure IF i'm correct!
    Also... not sure where to start!

    Hope this makes sense as I've rewritten it a dozen times!!

    Thanks for any suggestions/help

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    there a date function call datediff()

    mydays = datediff('d',[startdate],[enddate]) this will return the number of days between to dates

    I have a written a function that work out what financial year we are in


    Code:
    Function MMPYEAR(F)
    If IsNull(F) Then Exit Function
    f1 = year(F)
    f2 = Month(F)
    If f2 > 6 Then
    MMPYEAR = (f1 + 0) & "/" & (f1 + 1)
    Else
    MMPYEAR = (f1 - 1) & "/" & (f1 + 0)
    End If
    End Function
    you need to change the 6 to your month

    ThisYear = MMPYEAR([datevalue]) will return what f year we are in

    fyear = MMPyear(today())

    fyear = 2010/2011

    reading more of your post

    I think you have to write Function to return the days

    Code:
    function MyDates(StartDate,EndDate)
    
    FY1 = MMPYEAR(Startdate)
    FY2 = MMPYEAR(Endate)
    vb_Days = 0
    vb_EndDate = dateserial(year(startdate),3,31)
    vb_StartDate = dateserial(year(startdate),4,1)
    
    IF FY1<>FY2 then
    ' Differance Fyears so we do something here
    
    vb_Days = datediff('d',startdate,vb_Enddate)
    
    Else
    ' SAME Fyear
    
    End if
    
    
    
    End Function
    mine blank

    this is off top of head untested do it on the way to work
    Last edited by myle; 05-24-11 at 17:49. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    May 2011
    Posts
    6
    Thanks for the help.

    Its been over 8 years since I last seriously did any database work and im starting to piece things together bit by bit!

    In a query where do I put the functions? I'm guessing that I wouldnt and I'd use it directly in a form then use that for the report?



    Thanks
    Last edited by Fordyboy; 05-24-11 at 18:14. Reason: typo

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Put the Function in a Module

    It could used in both

    Then in the Query


    Fyear:MMPYEAR([feildname])
    Last edited by myle; 05-24-11 at 19:00. Reason: Spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    May 2011
    Posts
    6
    Thanks again for the help.

    Got the year function now working which is great.

    Still having difficulty with the other. Head is pounding as I've been working on this since about 3 this afternoon. Going to call it a night and have another look at it in the morning.

    Thanks again

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    This is not as straight-forward as it appears on first glance - I spent a long time hammering out something similar (relating to accounting for housekeeping windows in downtime reports).
    I've hammered out a quick truth table and worked out the following rules:
    • If both dates are less than or equal to the end date of the financial year, the visit occurred in the "old" financial year.
    • If the start date is less than or equal to the end date of the financial year and the end date is greater than the end date of the financial year, the visit is split between the two years
    • If both dates are greater than the end date of the financial year, the visit occurred in the "new" financial year

    Your query needs two calculated fields with between them will return at least one non-zero value showing the allocation for the visit between the financial years. The calculations themselves are rather trickier - I can look something up if you like?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    May 2011
    Posts
    6
    That would be great if you could.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    On further reflection, my example might not be relevant. It's checking an incidence of downtime against two potential windows each day, and has an If statement with seven ElseIf clauses!

    I've rearranged the truth table and illustrated nine scenarios, of which three are unlikely. I did this by considering three dates (01-Mar-2011, 31-Mar-2011 and 10-Apr-2011) in each combination. Three combinations result in a start date that is after the end date - these three can be ignored.

    That left me with six scenarios. Three of them gave visiting periods entirely in the "old" financial year. However, assuming that your data integrity is maintained with respect to the end date being greater than the start date, all three of these can be identified by the criterion "[EndDate] <= FinYearEnd". Two of them gave visiting periods that are split across financial years. These are identified by the criteria "[StartDate] <= FinYearEnd AND [EndDate] > FinYearEnd". The final one gave a visiting period entirely in the "new" financial year, and is identified by the criterion "[StartDate] > FinYearEnd" (again relying on data integrity).

    As to putting this into a query, it gets a bit trickier.
    Working on the following basis:
    The terms "old" and "new" are applied on the assumption that we are standing on the change point between financial years - in neither of them.
    [Person] - person ID field
    [StartDate] - start date field
    [EndDate] - end date field
    FinYearEnd() - function to return end date of financial year that contains a given date
    Your query can hold the first three fields and then the calculated fields as follows.
    Code:
    [FromOldAllowance] = IIf([StartDate] > FinYearEnd(), 0, IIf([EndDate] <= FinYearEnd(), DateDiff("d", [StartDate], [EndDate]) + 1, DateDiff("d", [StartDate], FinYearEnd()) + 1))
    [FromNewAllowance] = IIf([EndDate] < FinYearEnd(), 0, IIf([StartDate] <= FinYearEnd(), DateDiff("d", FinYearEnd(), [EndDate]) + 1, DateDiff("d", [StartDate], [EndDate]) + 1))
    These will return between one and two non-zero values.

    Apologies for the essay - I got carried away. Please note that this is untested, but it's based on calculations that seem to work.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    May 2011
    Posts
    6
    Didnt get a chance to try this today. Will have a look at it tomorrow.

    Thanks for your help!

  10. #10
    Join Date
    May 2011
    Posts
    6
    Spoken again to the end users of the database and discovered that this problem would only affect at max a dozen bookings each year.

    I've devised a work around which they are happy to try.

    However, I'm glad I asked the question as the coding I've acquired I've used in two other parts of the database so far to work out which financial year particular things have happened in.

    Thanks again for your help.

    Once I get everything else they are looking for working. I might pop back and take another deep look at the problem and get it working without the workaround.

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You're welcome!

    Once I get everything else they are looking for working...
    Hahahahahahahahaha! Good luck with that!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •