Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22

    Unanswered: Datediff function for year

    I'm trying to do a datediff function using years but I don't want the years to be calendar based but rather between the dates of 7/1 and 6/30.
    How would I go about doing that?

    Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look at the DateSerial() and Year() functions in the help files
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What exactly are you trying to do here? Using DateDiff() with the 'year' interval can be very inexact. Using December 31, 2011 and January 1, 2012, for instance, returns 1 year as the difference, even though you're talking about an actual difference of 1 day!

    Using DateDiff() with the month interval and dividing by 12 (and MOD 12, if you want the fraction of a year, as well) will give you a more exact difference, while using the day interval and dividing by 365 (and MOD 365) will give you an even more exact difference. And doing it in either of these manners makes your fiscal (I assume) year of 7/1 and 6/30 a moot point.

    But as I suggested, knowing exactly what you're attempting to do here could help us to help you.

    Linq ;0)>
    Last edited by Missinglinq; 04-24-12 at 22:55.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    The short version of what I'm trying to do is as follows:

    I have a table of client allocated yearly costs which runs from 7/1-6/30. In the event that a client gets discharged I want to accurately reflect the amount of units allocated as well as allocated cost based on the discharge date. To that effect I have the following code:

    A = " UPDATE Vendor INNER JOIN (tblCases INNER JOIN tblService ON tblCases.ClientID = tblService.ClientID) ON Vendor.VendorID = tblService.VendorID SET tblService.units = ([tblservice].[units])/(DateDiff(BillingFreq,[tblService].[StartDate],[tblService].[EndDate])+1)*(DateDiff(BillingFreq,[tblservice].[StartDate],[tblCases].[Discharge Date])+1), tblService.EndDate =[Forms]![frmcases].[Discharge Date], tblService.Cost = ([tblservice].[cost])/([tblservice].[Units])*(DateDiff(BillingFreq,[tblservice].[StartDate],[tblCases].[Discharge Date])+1) WHERE (((tblService.ClientID)="
    B = ") AND ((tblService.IsIncurred)=No)) AND ((tblservice.enddate)>= #"
    C = "#);"
    If Not IsNull(Me.Discharge_Date) Then
    strSQL = A & Me.ClientID.Value & B & Me.[Discharge Date] & C
    Debug.Print strSQL
    DoCmd****nSQL strSQL


    "BillingFreq" is a column that lists the charge as a monthly, weekly or one time charge. I listed one time charges as Year for the datediff so that the price and units won't change when I run this code.
    But "year" won't work in this case. So what can I do to make it work?
    Also is the proper code for calculating weelks "w" or "ww"?

    Thanks!

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry, but I have no idea what you're doing here; my fault, no doubt, but "ww" is the proper Interval for calculating DateDiff in weeks.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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