Difference between two dates in a query
Solved
jed
-
lami20j Posted messages 21506 Registration date Status Moderator, Security Contributor Last intervention -
lami20j Posted messages 21506 Registration date Status Moderator, Security Contributor Last intervention -
Hello,
I have a problem calculating the difference between two dates.
I created a table with the fields DATEDEB DATEFIN NBJABS
Initially, I update this table by filling in the dateDeb and dateFin fields from another table based on a selected period on a form
I then want to calculate the number of days between these two dates and put it in the NBJABS field
I tried:
DoCmd.RunSQL "UPDATE nomtable SET NBJABS = " & DateDiff("d", DATEDEB, DateFin) &
and
DoCmd.RunSQL "UPDATE nomtable SET NBJABS = DATEFIN - DATEDEB "
any other idea?
Thanks in advance
I have a problem calculating the difference between two dates.
I created a table with the fields DATEDEB DATEFIN NBJABS
Initially, I update this table by filling in the dateDeb and dateFin fields from another table based on a selected period on a form
I then want to calculate the number of days between these two dates and put it in the NBJABS field
I tried:
DoCmd.RunSQL "UPDATE nomtable SET NBJABS = " & DateDiff("d", DATEDEB, DateFin) &
and
DoCmd.RunSQL "UPDATE nomtable SET NBJABS = DATEFIN - DATEDEB "
any other idea?
Thanks in advance
Configuration: Windows XP Internet Explorer 7.0
3 answers
I don't have a good mastery of Access but I was able to read this on the internet:
In my opinion, the parameter depends on the language of your database.
Another site that can help you: https://www.techonthenet.com/access/functions/date/datediff.php
--
-- Alex at your service --
-- Don't forget to mark your topics as "Resolved" when you have the answer ;-) --
The DATEDIFF("interval"; date1; date2) function returns the time interval between 2 dates. The "j" interval returns the result in days, the "aaaa" interval returns the result in years, the "m" interval returns the result in months. Apparently, you should change your first parameter in your query. In my opinion, the parameter depends on the language of your database.
Another site that can help you: https://www.techonthenet.com/access/functions/date/datediff.php
--
-- Alex at your service --
-- Don't forget to mark your topics as "Resolved" when you have the answer ;-) --
I found the solution.
In fact, datediff is a VBA function but also an SQL function.
So the syntax is very simple:
DoCmd.RunSQL "UPDATE " & nom2 & " SET NBJABS = DATEDIFF('d', DATEDEB, DATEFIN)"
Thank you.
I found the solution.
In fact, datediff is a VBA function but also an SQL function.
So the syntax is really simple:
DoCmd.RunSQL "UPDATE " & nom2 & " SET NBJABS = DATEDIFF('d', DATEDEB, DATEFIN)"
Thank you.