Dates in reports

[rcollins]

at I have is a start and end date in my report. I need to put the value of days that I got, 619.19, into how many years, months and days. Please advise.

[hyperpau]

at I have is a start and end date in my report. I need to put the value of days that I got, 619.19, into how many years, months and days. Please advise.

use the DateDiff functions

example:
for days
=DateDiff("d","StartDate","EndDate")

form months
=DateDiff("m","StartDate","EndDate")

for years
=DateDiff("y","StartDate","EndDate")

I know not how to do all of the three in one control only

[missinglinq]

The problem, of course, is that all years don't have 365 days in them, and months can have 28, 29, 30 or 31 days! So any conversion from days to years/months/days is going to be slightly inaccurate. With that in mind, from the Objects Dialog box goto Modules. Click on New and paste in this code:

Function D2YMD(No As Integer) As Variant
Dim Y As Long
Dim M As Long
Dim D As Long
 
Y = Int(No / 365)
M = Int((No - (Int(No / 365) * 365)) / 30)
D = No - ((Y * 365) + (M * 30))
 
D2YMD = Y & " years " & M & " months " & D & " days"
End Function
 

When Access asks if you want to save the module (it'll probably be called Module1 at this point) answer Yes and name it DaysConversions.

Then in your report place an unbound textbox and give as it's Control Source

=D2YMD(DateDiff("d","StartDate","EndDate"))

Good Luck!

Linq ;0)>

[rcollins]

Can I just change this
=D2YMD(DateDiff("d","StartDate","EndDate"))
which works fine
to this to get an average?
=Avg(D2YMD(DateDiff("d","StartDate","EndDate")))
It gives me a data type mismatch

[rcollins]

When th formula figures start to end date, I get an error. Of course, there are people who have not finished their jobs. How can I set a null end time to Date()?

[rcollins]

[Ok, so tI got it working. to use Nz([EndDate],Date()) to get the current date in the field. That workedeg good. The only thing I didn't quite understand, it hte code that you gave me, I put in a seperate text box the refered to it from the one I wanted the avg. All worked, though. Thank you for your help.

[missinglinq]

D2YMD(DateDiff("d","StartDate","EndDate") returns a string with letters and numbers in it so you can't "average" a string with mixed characters and hence the mismatch. Glad you got it working for you!

Linq ;0)>

[rcollins]

Now I need to figure the lenght of time that it took the clients to find a job. Right now, I have it set to average from the date of registration to the start date, the problem is that it takes all of there jobs, I want just the first one.

Also, with average hours worked and average rate of pay, I only want this to come from the current job. How can I do this using the null value for the end date?