How do I calculate year and month in two fields in an access quer. Compiling and Confused 29.09.2006 00:12:02 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.

RE: How do I calculate year and month in two fields in an access quer. Mr B 29.09.2006 02:57:01 Take a look in the Access Help file for the DateDiff function. -- HTH Mr B "Compiling and Confused" wrote: [Quoted Text] > 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.

Re: How do I calculate year and month in two fields in an access quer. fredg <fgutkind[ at ]example.invalid> 29.09.2006 03:41:37 On Thu, 28 Sep 2006 17:12:02 -0700, Compiling and Confused wrote: [Quoted Text] > 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. Check "A More Complete DateDiff Function" at http://www.accessmvp.com/djsteele/Diff2Dates.html -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail

RE: How do I calculate year and month in two fields in an access quer. Klatuu 29.09.2006 15:25:01 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. "Compiling and Confused" wrote: [Quoted Text] > 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.

RE: How do I calculate year and month in two fields in an access q Compiling and Confused 29.09.2006 17:41:02 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" wrote: [Quoted Text] > 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. > > "Compiling and Confused" wrote: > > > 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.

RE: How do I calculate year and month in two fields in an access q Klatuu 29.09.2006 17:48:02 "Compiling and Confused" wrote: [Quoted Text] > 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" wrote: > > > 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. > > > > "Compiling and Confused" wrote: > > > > > 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.

RE: How do I calculate year and month in two fields in an access q Klatuu 29.09.2006 17:54:02 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" wrote: [Quoted Text] > 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" wrote: > > > 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. > > > > "Compiling and Confused" wrote: > > > > > 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.