updating a field using datediff

[Whooo]

when updating a field that is a text field using the datediff function, decimals are displayed.

when updating a field that is a number (fixed 2 decimals) the datediff updates the field with a rounded number (no decimals) go figure?

datediff("m",[start],[finish])\60

start is a text field 08:15
finish is a text field 10:00
via an update query
if i replace a text field i get 1.75 hours
if I replace a number field i get 2 hours

Cogito ergo sum - I think?

 

[JeremyNYC]

DateDiff is for comparing dates, not integers. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Whooo]

Thanks,

I am using it to determine the time difference and it works okay. It is the issue of when I save it to a number field it rounds up but when I save it to a text field it works, It also works when I do a query. It is only when I do an update to a file that it doesn't seem to work Cogito ergo sum - I think?

 

[cmmrfrds]

Date data type is stored as a decimal number in any of the relational databases.
i.e. 1.1426504629635
The time part of this number is 3:25:35
3 hours 25 minutes 25 seconds.

In Access the integer part of the number is the number of days since 12/31/1899 so in the above number it is 01/01/1900 03:25:25

The different date functions can interpret this number.
datepart(), dateadd(), datediff(), etc...

The parameter to the functions operate on different parts of the number. datediff("m",start,finish) will extract the month in your example 2. datediff("n",start,finish) will extract the minutes in your example 2 and so on. So, typically to get hours and minutes you need to do 2 calculations and concatenate the results.

Example of another way using a decimal number for the date.
Dim theTime As Double
theTime = theDate1 - theDate2
Debug.Print "The time = "; theTime
totalhours = Int(CSng(theTime * 24) Mod 24)
Debug.Print "The hours = "; totalhours
totalminutes = Int(CSng(theTime * 1440) Mod 60)
Debug.Print "The minutes = "; totalminutes
totalseconds = Int(CSng(theTime * 86400) Mod 60)
Debug.Print "The seconds = "; totalseconds
'- concatenate the results
Debug.Print "Time = "; totalhours & ":" & totalminutes & ":" & totalseconds

Hope this aids in understanding date arthmetic.

 

[Whooo]

thanks for the explanation and suggestion, in the past I used the "s" parameter to get seconds then divide by 60 and then the result by 60 again to get the number of days. It seemed to work on hours within the same day.







Cogito ergo sum - I think?