I am having some problem with a Year Function.
I have form on which I have 4 field which indicate dates and an
additional form which sums those dates:
These are the fields:
YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2
In each of DATE fields, I wil be adding a date. The YEARS field
should sum the values I enter in the other 4 fields.
I have tried simply to add the two other fields to the code below, but
it
does not work:
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2])
I have also tried to use two other not visible fields, named A and, A1
and fill them with your origianal code and then add in the YEARS
field: =[A]+[A1].
This seems some solution, the only problem is that it may happen that
sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When
this happens, it does not make to calculation. If these fields do show
dates, the calculations is processed.
Any further help?
Thanks.
4  11917 
>
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]
)
From MS Access 2000 Help Files:
Syntax
DateDiff(interv al, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function only allows for calculating of _one_ pair of dates at
a time. So to remedy your problem I would do the following:
<aircode>
=(DateDiff("yyy y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy" ,[STARTINGDATE2],[ENDINGDATE2]))
</aircode>
Keep in mind that you could get rounding errors of up to 2 years using such
large units of mesure on each end of the + symbol. Here is an alternative
solution that should yield in a smaller round error.
<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>
This second piece of code calculates the difference in days, leaving
rounding errors of a day or so, then after we have added our two sets of
days together we then divide by the number of days in a year (365) to get
the number of years.
I would give the help file a read on DateDiff, it has some very useful
information for that function.
HTH
Mike Krous
"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com... I am having some problem with a Year Function.
I have form on which I have 4 field which indicate dates and an
additional form which sums those dates:
These are the fields:
YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2
In each of DATE fields, I wil be adding a date. The YEARS field
should sum the values I enter in the other 4 fields.
I have tried simply to add the two other fields to the code below, but
it
does not work:
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]
)
I have also tried to use two other not visible fields, named A and, A1
and fill them with your origianal code and then add in the YEARS
field: =[A]+[A1].
This seems some solution, the only problem is that it may happen that
sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When
this happens, it does not make to calculation. If these fields do show
dates, the calculations is processed.
Any further help?
Thanks.
Note:
If you use something like the second method I suggested, you may have to
wrap the whole function inside the Fix() function to get rid of the
decimal....Fix( ) is just one of many that my suite your formating needs,
maybe Int() or Format() also.
something like:
=Fix(rest of formula here)
Mike Krous
"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:Y8******** ************@co mcast.com...
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] )
From MS Access 2000 Help Files:
Syntax
DateDiff(interv al, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function only allows for calculating of _one_ pair of dates
at a time. So to remedy your problem I would do the following:
<aircode>
=(DateDiff("yyy y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy" ,[STARTINGDATE2],[ENDINGDATE2]))
</aircode>
Keep in mind that you could get rounding errors of up to 2 years using
such large units of mesure on each end of the + symbol. Here is an alternative
solution that should yield in a smaller round error.
<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>
This second piece of code calculates the difference in days, leaving
rounding errors of a day or so, then after we have added our two sets of
days together we then divide by the number of days in a year (365) to get
the number of years.
I would give the help file a read on DateDiff, it has some very useful
information for that function.
HTH
Mike Krous
"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com... I am having some problem with a Year Function.
I have form on which I have 4 field which indicate dates and an
additional form which sums those dates:
These are the fields:
YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2
In each of DATE fields, I wil be adding a date. The YEARS field
should sum the values I enter in the other 4 fields.
I have tried simply to add the two other fields to the code below, but
it
does not work:
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] )
I have also tried to use two other not visible fields, named A and, A1
and fill them with your origianal code and then add in the YEARS
field: =[A]+[A1].
This seems some solution, the only problem is that it may happen that
sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When
this happens, it does not make to calculation. If these fields do show
dates, the calculations is processed.
Any further help?
Thanks.
Thanks Mike, I am using yuor second code (/365) and works fine. There
is by the way still one thing that I would like to ask.
It may happen that sometimes I will not complete the STARTINGDATE2 and
ENDINGDATE2 fields but just the first two ones therofore I would like
my YEARS field to be able to sum only just the first two fields.
The way I wanted is that the YEARS field should always show the date
difference between the STARTINGDATE1 and ENDINDATE1 fields, then, if I
will complete the STARTINGDATE2 and ENDINGDATE2 fields, well then the
difference should be summed to the first result.
Is this possible? Thanks
"Mike Krous" <m.krous@nospam _comcast.net> wrote in message news:<ZP******* *************@c omcast.com>... Note:
If you use something like the second method I suggested, you may have to
wrap the whole function inside the Fix() function to get rid of the
decimal....Fix( ) is just one of many that my suite your formating needs,
maybe Int() or Format() also.
something like:
=Fix(rest of formula here)
Mike Krous
"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:Y8******** ************@co mcast.com...
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] )
From MS Access 2000 Help Files:
Syntax
DateDiff(interv al, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function only allows for calculating of _one_ pair of dates
at a time. So to remedy your problem I would do the following:
<aircode>
=(DateDiff("yyy y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy" ,[STARTINGDATE2],[ENDINGDATE2]))
</aircode>
Keep in mind that you could get rounding errors of up to 2 years using
such large units of mesure on each end of the + symbol. Here is an alternative
solution that should yield in a smaller round error.
<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>
This second piece of code calculates the difference in days, leaving
rounding errors of a day or so, then after we have added our two sets of
days together we then divide by the number of days in a year (365) to get
the number of years.
I would give the help file a read on DateDiff, it has some very useful
information for that function.
HTH
Mike Krous
"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com... I am having some problem with a Year Function.
I have form on which I have 4 field which indicate dates and an
additional form which sums those dates:
These are the fields:
YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2
In each of DATE fields, I wil be adding a date. The YEARS field
should sum the values I enter in the other 4 fields.
I have tried simply to add the two other fields to the code below, but
it
does not work:
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] )
I have also tried to use two other not visible fields, named A and, A1
and fill them with your origianal code and then add in the YEARS
field: =[A]+[A1].
This seems some solution, the only problem is that it may happen that
sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When
this happens, it does not make to calculation. If these fields do show
dates, the calculations is processed.
Any further help?
Thanks.
Paolo-
yes this is totally possible. In my post I said to use the following: <aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y" ,[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>
In order to seperate that information better I would do the following: In
your query I would seperate the formula above into two formulas then add
them together later.
column1 - get diff of starting date1, and ending date1, wrapped the function
inside of a Nz() function, the Nz() function will make sure that I at least
get a 0 for my calculation if for some odd reason I would have gotten a
null. This will make sure Ive got something to add to later.
=Nz(DateDiff("y ",[STARTINGDATE1],[ENDINGDATE1]),0)
column2 - get the diff of starting date 2 and ending date 2.
=Nz(DateDiff("y ",[STARTINGDATE2],[ENDINGDATE2]),0)
column3 - add column 1 and column 2 together then divide by 365 to get total
years.
=((column1 + column2) / 365)
Note: columns 1 and 2 are still in day format. when you display them in a
control you could use the following to get back to years:
=column1 / 365 or column2 / 365
everytime you see column1 and column2 you will have to replace the words
"column1" and "column2" with your actual names. Alternatively you could get
the time in years from start1 end1 and start2 end2 by using the following
additional columns in your query.
column4
=column1 / 365
column5
=column2 / 365
then you can simply place column 4 and 5 into your form without any
calculations.
now with the above columns you have everything broke out into pieces.
Column1 holds the difference between start1 and end1 and column2 holds the
difference between start2 and end2, column3 simply has the total between the
two differences divided by 365 to get years. The added columns 4 and 5
simply take columns 1 and 2 and bring them down into years.
HTH
Mike Krous
"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com... Thanks Mike, I am using yuor second code (/365) and works fine. There
is by the way still one thing that I would like to ask.
It may happen that sometimes I will not complete the STARTINGDATE2 and
ENDINGDATE2 fields but just the first two ones therofore I would like
my YEARS field to be able to sum only just the first two fields.
The way I wanted is that the YEARS field should always show the date
difference between the STARTINGDATE1 and ENDINDATE1 fields, then, if I
will complete the STARTINGDATE2 and ENDINGDATE2 fields, well then the
difference should be summed to the first result.
Is this possible? Thanks
"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:<ZP******* *************@c omcast.com>... Note:
If you use something like the second method I suggested, you may have to
wrap the whole function inside the Fix() function to get rid of the
decimal....Fix( ) is just one of many that my suite your formating needs,
maybe Int() or Format() also.
something like:
=Fix(rest of formula here)
Mike Krous
"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:Y8******** ************@co mcast.com... >
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] )
From MS Access 2000 Help Files:
Syntax
DateDiff(interv al, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function only allows for calculating of _one_ pair of
dates at a time. So to remedy your problem I would do the following:
<aircode>
=(DateDiff("yyy y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy" ,[STARTINGDATE2],[ENDINGDATE2]))
</aircode>
Keep in mind that you could get rounding errors of up to 2 years using
such large units of mesure on each end of the + symbol. Here is an
alternative solution that should yield in a smaller round error.
<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>
This second piece of code calculates the difference in days, leaving
rounding errors of a day or so, then after we have added our two sets
of days together we then divide by the number of days in a year (365) to
get the number of years.
I would give the help file a read on DateDiff, it has some very useful
information for that function.
HTH
Mike Krous
"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com...
> I am having some problem with a Year Function.
> I have form on which I have 4 field which indicate dates and an
> additional form which sums those dates:
>
> These are the fields:
>
> YEARS
> STARTINGDATE1
> ENDINGDATE1
> STARTINGDATE2
> ENDINGDATE2
>
> In each of DATE fields, I wil be adding a date. The YEARS field
> should sum the values I enter in the other 4 fields.
>
> I have tried simply to add the two other fields to the code below,
but > it
> does not work:
>
>
=DateDiff("yyyy ",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] )
>
> I have also tried to use two other not visible fields, named A and,
A1 > and fill them with your origianal code and then add in the YEARS
> field: =[A]+[A1].
>
> This seems some solution, the only problem is that it may happen
that > sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty.
When > this happens, it does not make to calculation. If these fields do
show > dates, the calculations is processed.
> Any further help?
>
> Thanks.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: CJM |
last post by:
I have an ASP page that lists files and folders in a directory. I'm using a
cookie to record the last time this page was visited, and I intend to show
links that are created/modified from that date minus 30mins to the
present... eg effectively new and modified files:
If DateDiff("n", subfolder.DateLastModified, sLastVisit ) < 30 Then.... etc
On my test machine this mechanism works OK, but on the live server (Win2k,
IIS5) I get some...
|
by: Drago |
last post by:
Hi, I got the next question, I want to know the diference between two
dates
and thats is really easy, but my problem is get that diference in the
following format
ex. "the diference is= 0 year, 9 months, 11 days"
var1 = 04/04/1999
var2 = 01/11/2000
Response.Write("var1 to var2 is " & DateDiff("d", var1, var2) & " days
|
by: inamori |
last post by:
I face that problems
07/01/2003 06/30/2006 ---------> it should be 3
01/01/2003 02/28/2005 --------->could i get 2 years and 2 months
01/01/2003 03/01/2005 --------->could i get 2 years and 2 months and 1
|
by: Lofty |
last post by:
Hi all.
I have to write an app that interacts with mySQL (I really must have done
some evil, evil stuff in a previous life to be landed with this!)
I need to work out the difference in days between values in the database and
the current date. "No problem," thought I , "I'll just use the SQL DATEDIFF
command." Heh! Well, the user interface I'm using didn't even recognise
DATEDIFF as being a function, so I decided to visit the mySQL...
|
by: PMBragg |
last post by:
ORINGINAL Post
>Thank you in advance. I'm trying to pull all inventory items from December
>of the previous year back to 4 years for my accountant. I know this can be
>done, but I'm drawing a blank. I've tried;
>
>DateDiff("y",-4,DateIn) and get errors
>
>Please any assistance would be greatly appreciated.
>
| | |
by: Adrian |
last post by:
I hit on this problem converting a VB.NET insurance application to C#.
Age next birthday calculated from date of birth is often needed in
insurance premium calculations.
Originally done using DateDiff in VB.NET which is only available in C#
if you don't mind linking in Microsoft.VisualBasic.dll to your C#
application.
I wanted to avoid this so set about a pure C# solution which uses a
combination of TimeSpan in whole days and the...
|
by: kevinjwilson |
last post by:
I am trying to get the date difference between two dates but I don't
want the function to include weekends in the calculation. Does anyone
have an idea on how to make this work?
|
by: khyati30 |
last post by:
hi frds i have problem is datediff fucnctions.
how can i find the days from two days
means start date='03-08-08' (m-d-y)
end date='04-07-08' (m-d-y)
date difference is 30 days .
|
by: muddasirmunir |
last post by:
i am using vb 6 , i had place two datepicker in form now i want to calcuate differcen of month in two date for this i used the function datediff
i had try it withh many syntax but getting error like
datediff(mm,date1,date2)
datediff(mmm,date1,date2)
datediff(month,date1,date2)
datediff("mm",date1,date2)
datediff("mmm",date1,date2)
datediff("month",date1,date2)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
