View original (French)

Difference between two dates in a query

Solved
jed -  
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
Configuration: Windows XP Internet Explorer 7.0

3 answers

  1. Anonymous user
     
    I don't have a good mastery of Access but I was able to read this on the internet: 
    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 ;-) --
    1
    1. jed
       
      Re,
      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.
      0
    2. jed
       
      Re,
      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.
      0
  2. Anonymous user
     
    On what database do you work? (Oracle, MySQL, PostgreSQL ...)
    --
    -- Alex at your service --

    -- Don't forget to mark your topics as "Resolved" when you have the answer ;-) --
    0
    1. jed
       
      Thank you for responding to me.

      Yes, I forgot to specify.

      I work with Access and I integrate my queries in Visual Basic.
      0
  3. Anonymous user
     
    Nikel, please set your post to resolved ;-)
    --
    -- Alex at your service --

    -- Don't forget to mark your topics as "Resolved" when you have the answer ;-) --
    0
    1. lami20j Posted messages 21506 Registration date   Status Moderator, Security Contributor Last intervention   3 571
       
      It's done ;-)
      --
      106485010510997108
      0