Date difference

[rdawadiuk]

hi,
I would like to calculate the date difference between two dates using field names which I have in my table.
The field name is " Account opened on" for eg
If the "Account open on" is Jan 5, 2007 the I would like to calculate the date difference between this date and the end of the quarter (March 31 2007) I would like Acess to calculate this difference automatically without me entering the end of the quarter date.

please help
thanks

[FishVal]

Hi, there.

Do you mean difference between two dates stored in your table or between a date stored in your table and end of the quarter the date belongs to?

[rdawadiuk]

The date "Account opened on" is stored in my table and I have to calculate the date difference between it and the quarter it belongs to (this is not stored in my table)

thanks

[FishVal]

The date "Account opened on" is stored in my table and I have to calculate the date difference between it and the quarter it belongs to (this is not stored in my table)

thanks

Well.

The following function will return end of the quarter the date belongs to

Public Function GetEndOfTheQuarter(dteInput As Date) As Date
 
    Dim intMonth As Integer
 
    intMonth = Int((Month(dteInput) - 1) / 3) * 3 + 4
    GetEndOfTheQuarter = DateAdd("d", -1, DateSerial(Year(dteInput), intMonth, 1))
 
End Function
 

the same as expression in query

SELECT DateAdd("d", -1, DateSerial(Year([dteYourDate]), Int((Month([dteYourDate]) - 1) / 3) * 3 + 4, 1)) AS dteEndOfTheQuarter FROM tblYourTable;
 

Use DateDiff function to calculate difference between dates.

[rdawadiuk]

Thank you very much. You saved me

[FishVal]

You are welcome.

Best regards,
Fish

[missinglinq]

Lovely piece of logic, FishVal!

Linq ;0)>

[FishVal]

Thanks, Linq.