How do I calculate year and month in two fields in an access quer.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working in a database where I build profiles, i have general information
that I need to turn into more specific information on a report. For example,
I have the birthdate of a person and I've pulled that into a query which
calculates their age so that I can show that on a report. The issue I am
facing now is calculating how much time in years and months that a person has
been working which is derived from their "in service date" I have the code to
calculate the year, but I don't have the code to calculate the months.
 
I am working in a database where I build profiles, i have general information
that I need to turn into more specific information on a report. For example,
I have the birthdate of a person and I've pulled that into a query which
calculates their age so that I can show that on a report. The issue I am
facing now is calculating how much time in years and months that a person has
been working which is derived from their "in service date" I have the code to
calculate the year, but I don't have the code to calculate the months.
Click to expand...

Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html
 
The issue here is that date data types refer to a specific point in time.
Elapsed time is not the same as a point in time. So no date function, on its
own, will give you that information.

They way to calculate intervals of time is to first determine what the
smallest interval of time you want to return. In your case, it is months.
To do that, you use the DateDiff function to calculate that value:

intTotMonths = DateDiff("m", dtmStartDate, dtmEndDate)

Since you want to display years and months, you need to separate those.
First we can calculate the years by dividing the total months by the number
of months in a year. The best way to do this is to use the \ operator, which
does a division and returns on the integer part of the results.

intYears Worked = intTotMonths \ 12

Now we need to know how many months are left over. For this we use the Mod
operator which returns only the remainder of the division

intMonths Workded = intTotMonths Mod 12

That will give you the years and months.
 
Thank you for the helpful input. The function that I am using to calculate
the year is as follows:

Int((Date()-[Job Entry Date])/365.25) - for years

How do I then get the remaining months from this expression?
 
Compiling and Confused said:
Thank you for the helpful input. The function that I am using to calculate
the year is as follows:

Int((Date()-[Job Entry Date])/365.25) - for years

How do I then get the remaining months from this expression?

Klatuu said:
The issue here is that date data types refer to a specific point in time.
Elapsed time is not the same as a point in time. So no date function, on its
own, will give you that information.

They way to calculate intervals of time is to first determine what the
smallest interval of time you want to return. In your case, it is months.
To do that, you use the DateDiff function to calculate that value:

intTotMonths = DateDiff("m", dtmStartDate, dtmEndDate)

Since you want to display years and months, you need to separate those.
First we can calculate the years by dividing the total months by the number
of months in a year. The best way to do this is to use the \ operator, which
does a division and returns on the integer part of the results.

intYears Worked = intTotMonths \ 12

Now we need to know how many months are left over. For this we use the Mod
operator which returns only the remainder of the division

intMonths Workded = intTotMonths Mod 12

That will give you the years and months.
Click to expand...
Click to expand...
 
Sorry about the blank reply, I clicked post by mistake.

You wont be able to do this in one line. You will need to write a function
to do the calculation.
Function TimeInService(dtmDateOfHire) As String
Dim intTotMonths As Integer
Dim intYearsWorked As Integer
Dim intMonthsWorked As Integer

intTotMonths = DateDiff("m", dtmDateOfHire, Date)
intYearsWorked = intTotMonths \ 12
intMonths Workded = intTotMonths Mod 12

TimeInService = Cstr(intYearsWorked) & " Years And " &
Cstr(intMonthsWorked) & " Months"
End Function
Compiling and Confused said:
Thank you for the helpful input. The function that I am using to calculate
the year is as follows:

Int((Date()-[Job Entry Date])/365.25) - for years

How do I then get the remaining months from this expression?

Klatuu said:
The issue here is that date data types refer to a specific point in time.
Elapsed time is not the same as a point in time. So no date function, on its
own, will give you that information.

They way to calculate intervals of time is to first determine what the
smallest interval of time you want to return. In your case, it is months.
To do that, you use the DateDiff function to calculate that value:

intTotMonths = DateDiff("m", dtmStartDate, dtmEndDate)

Since you want to display years and months, you need to separate those.
First we can calculate the years by dividing the total months by the number
of months in a year. The best way to do this is to use the \ operator, which
does a division and returns on the integer part of the results.

intYears Worked = intTotMonths \ 12

Now we need to know how many months are left over. For this we use the Mod
operator which returns only the remainder of the division

intMonths Workded = intTotMonths Mod 12

That will give you the years and months.
Click to expand...
Click to expand...
 
Back
Top