# Thread: Datediff function for year

1. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
Look at the DateSerial() and Year() functions in the help files

3. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
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 21:55.

4. Registered User
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. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
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)>

#### Posting Permissions

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