about period calculating

[romanticode]

i have query tthat calculate perioe between date. the result is number.
how can i modify so it will show something like 1 year, 2 moths 3 days?

Thanks

 

[raskew]

Hi -

Try placing this in a standard module, then calling it as shown:

Function fAgeYMD(StartDate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years, months and days
'Coded by:  raskew
'To call:
' ? fAgeYMD(#7/6/54#, #10/3/84#)
'Returns:
' 30 years 2 months 28 days

Dim inthold As Integer
Dim dayHold As Integer

   inthold = Int(DateDiff("m", StartDate, EndDate)) + _
             (EndDate < DateSerial(year(EndDate), month(EndDate), Day(StartDate)))
             
   If Day(EndDate) < Day(StartDate) Then
      dayHold = DateDiff("d", StartDate, DateSerial(year(StartDate), month(StartDate) + 1, 0)) + Day(EndDate)
   Else
      dayHold = Day(EndDate) - Day(StartDate)
   End If
   
   fAgeYMD = Int(inthold / 12) & " year" & IIf(Int(inthold / 12) <> 1, "s ", " ") _
             & inthold Mod 12 & " month" & IIf(inthold Mod 12 <> 1, "s ", " ") _
             & LTrim(str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "")

End Function

HTH - Bob

 

[romanticode]

sorry i'm newbie.
the calculation is placed on query. i just place one button on my form to show the calculation. just say it's data for book rental periode.
So where i sould place the function? on button code or query?
can u give example? thnks

 

[raskew]

Hi -

To call it (calculating current age in this example) from a query:

SELECT LastName, FirstName, BirthDate, [b]fageYMD([birthdate],Date()) AS age[/b]
FROM Employees;

Bob

 

[romanticode]

this module give me result looks like "xx years, xx monts". my date format on database is mm/dd/yyyy. so, on calculation which result 1, that's mean 1 day, it said "108 years ...."

How can i fix this?

 

[raskew]

Hi -

Please post the code you used to call this, including the dates used.

Bob

 

[romanticode]

fageYMD(Date()-[TANGGAL],Date()) AS Expr1

complete sql query.

SELECT NOTA.KODE_LANG, ALAMAT.NAMA, NOTA.NAMA3, NOTA.KD_SALES, NOTA.TANGGAL, fageYMD(Date()-[TANGGAL],Date()) AS Expr1, NOTA.NOTA, NOTA.KURIKULUM, NOTA.RABAT, NOTA.JUAL, NOTA.BAYAR, NOTA.RETUR, NOTA.SALDO
FROM ALAMAT RIGHT JOIN NOTA ON ALAMAT.KODE = NOTA.KODE_LANG
WHERE (((NOTA.SALDO) Not Like "0"))
ORDER BY NOTA.TANGGAL;

tanggal is on m/d/yyyy format

thanks

 

[raskew]

Hi-

Look at the example. It calls for just two arguments, StartDate and Enddate, not fageYMD(Date()-[TANGGAL],Date()).

Bob

 

[romanticode]

so that module only count between dates?

any idea to fix my problem? pls

thks

 

[boblarson]

This SHOULD be it:

fageYMD([TANGGAL],Date())

It doesn't make sense to subtract TANGGAL from the current date because the function is actually determining the length of time between Tanggal and the current date.