Start and End Dates

[Qualinwraith]

I have a query that is generating two colums, one the states the Start Date and the other that States the End Date. I need to display another column that displays the time difference between the two. Any ideas how?

 

[Cereldine]

have a look at the dateDiff pieces in the help files

 

[reclusivemonkey]

Try using the DateDiff function. Not sure what you mean by "time" though. The smallest unit DateDiff has is days.

 

[Qualinwraith]

it did help but unfortunately since I'm generating the colums with the dates out of a query, when I run the whole thing it is asking me to insert the First Date and Last Date manually and not accepting the existing columns

 

[Mile-O]

Try using the DateDiff function. Not sure what you mean by "time" though. The smallest unit DateDiff has is days.

reclusivemonkey, the smallest unit DateDiff can handle is seconds.

 

[reclusivemonkey]

reclusivemonkey, the smallest unit DateDiff can handle is seconds.

Oh right. My text book doesn't have anything about seconds in it, but then again it is for Access 2000.

 

[Mile-O]

Oh right. My text book doesn't have anything about seconds in it, but then again it is for Access 2000.

No need to read the textbook. Access does come with a help file at the press of a button.

 

[Bat17]

Sounds like you have the SQL wrong, can you post it please

Peter

 

[Mile-O]

it did help but unfortunately since I'm generating the colums with the dates out of a query, when I run the whole thing it is asking me to insert the First Date and Last Date manually and not accepting the existing columns

You will need to base a second query off this query as you can't use calculations in a query whilst it calculates. Of course, I'd like to see your SQL too.

 

[Qualinwraith]

This is my current SQL statement. Note that this one is asking me to enter the values manually instead of taking those found in the columns.

SELECT tblMoscow_Transactions.FK_PledgeID, Max(tblMoscow_Transactions.intStatus) AS [Last Status], Min(tblMoscow_Transactions.dtmStatusDate) AS [First Date], Max(tblMoscow_Transactions.dtmStatusDate) AS [Last Date], DateDiff("y",[First Date],[Last Date]) AS DaysElapsed
FROM tblMoscow_Transactions
GROUP BY tblMoscow_Transactions.FK_PledgeID, DateDiff("y",[First Date],[Last Date]);

 

[Bat17]

Have you tried using the Min/Max in the Datediff?

DateDiff("y",Min(tblMoscow_Transactions.dtmStatusDate) ,(tblMoscow_Transactions.dtmStatusDate) );

Peter