Getting the duration between two date fields in a table in Microsoft Access RSS

• • Reply
  

  icenvon

  Member

  36 Points

  35 Posts

  Getting the duration between two date fields in a table in Microsoft Access

  Sep 15, 2011 12:51 AM|LINK

  Hello. I have three fields(Columns) in a CampActivity table in Microsoft Access 2007: CloseDate, OpenDate, and CampDuration.

  I want camp duration to reflect the time difference between CloseDate and OpenDate. So I put this code in the CampActivity table validation rule:

  [CampDuration] = DateDiff("d", [OpenDate], [CloseDate]) .

  But that didn't work. So I put the code in the CampDuration default value, but it gave an error message saying the database engine does not recognize either the field 'OpenDate' in a validation expression, or the default value in the table 'ClosedTickets'. When i put it in the validation rule of CampDuration, it gave an error message saying Invalid SQL syntax - cannit use multiple columns in a column-level CHECK constraint.

  Kindly tell me where I can apply this code in either the table or the field so it can work or rather how, in Microsft Access 2007.

  Thanks

• • Reply
  

  hans_v

  All-Star

  35998 Points

  6551 Posts

  Re: Getting the duration between two date fields in a table in Microsoft Access

  Sep 15, 2011 09:06 AM|LINK

  icenvon

  have three fields(Columns) in a CampActivity table in Microsoft Access 2007: CloseDate, OpenDate, and CampDuration

  There' no need to store the Campduration in your table, because you can always calculate it based upon the other 2 fields...

  icenvon

  I want camp duration to reflect the time difference between CloseDate and OpenDate. So I put this code in the CampActivity table validation rule:

  [CampDuration] = DateDiff("d", [OpenDate], [CloseDate])

  Create a query like this:

  SELECT OpenDate, CloseDate, DateDiff("d", [OpenDate], [CloseDate]) AS CampDuration FROM CampActivity

   

  • Edited by hans_v on Sep 15, 2011 09:07 AM
  • Marked as answer by decker dong - msft on Sep 17, 2011 12:38 AM
• • Reply
  

  icenvon

  Member

  36 Points

  35 Posts

  Re: Getting the duration between two date fields in a table in Microsoft Access

  Sep 15, 2011 07:14 PM|LINK

  Hans_V, thanks  A MILLION!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  You made my day, boss