Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    10

    Unanswered: dates compares in form

    Hi there

    A question from a newbie, hope some one could help me.

    I have a table with some date fields in among others.
    One field is called “Start production” and another is called “Stop production”
    I want to do a comparison between “Stop production” and “Start production” at the NEXT record! It is meant to get an msgBox with a warning if time between “Stop production” and “Start production” at next record exceed e.g. 5 minutes

    recID Start production Stop production

    1011 2007-10-01 15:33 2007-10-01 16:11
    1012 2007-10-01 16:12 2007-10-01 18:32
    1013 2007-10-01 18:41 2007-10-01 19:13
    1014 2007-10-01 19:14 2007-10-01 21:36

    I hope it is not too messy!

    Best Regards

    Gostap

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    see function

    See function Datediff in help

  3. #3
    Join Date
    Nov 2006
    Posts
    10
    Yes, but i want to compare previous StopProduction with next record StartProduction, and I dont know how to do that. I know how to compare StartProduction and StopProduction in the same record

    BG

    GOstap

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re

    1. You know the ID of the row your on now
    2. Get the date of this record
    3. Do a rs.moveprevius or make a select with "where id =(id-1)"
    4. get the 2nd Date
    5. Do a datediff between Date1 and date 2
    6. Give a msg box
    7. Done

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can you rely in the id being in sequence? ID's may be out of sequecne (they reflect the order the recrods were entered, not neccesarily the order in the real world.. if you had more than pone person entering the data concurrently then there is a strong possibility the ID@s may be out of sequence.

    if the user presses cancel whilst part way through enterign a record then there is a risk you willthe records will no longer be in numerical sequence.... a deleted record, or partially filled in and unsaved record will leave a gap.

    are you dealing with a single machine or singloe process?.
    does the process run 24hours a day, 7 days a week?

    There is no easy way that I can think of to achieve what you want, short of writing your own function to calulate the difference.

    however you could fake it in a form or report.
    in a report store the current records end date, then subtract it formt eh next records start date and take appropriate action.... appropriate action could be to suppress printing if the changeover was less than 5 minutes, or change text colour as required (say amner for 4...8 minutes, red for 8+ minutes) remember to reset the clocks if you are using grouping
    remember to allow for shift changes...
    remember to allow for time off or holidays.. again you will haev to reset tthe clock

    if you are doing this as part of data entry then I think you have a slightly easier problem..pull up the previous record, enter new data perfomr you subtraction using datediff.

    the alternative is a function which takes as a parameter (say) the current start time and returns the time difference between that satrt date and the last known appropriate finish date.

    the basic function should be soemthign like
    check parameters.. make sure they are valid
    set up some default values
    open a recordset
    query the db to find the nearest record with a finish date which is less than or equal to the supplied date
    make whatever post query processing you need to do (eg check the days are the same...
    calculate the difference
    return the value

    so the SQL qould be something like
    select finishdate from mytable where finishdate <= #mydatevalue# order by finish date desc

    the # is used to encapsulate a date value so that the db engine knows its processing a date rather than a equation. sometimes you need to specify the date US style eg mm/dd/yyyy
    the first record in that recordset should be THE one

    you could extend that function so that it returns a boolean value (a true false) by say including a time difference threshold in say minutes. that way round you can use the function in the form

    if myfunction(triggerdate,10) = true then action a, else action b
    returning the actual values makes sense if you are going to use the value in several places.. if its only used once then a boolean value may make reading your code clearer and easier to understand.

  6. #6
    Join Date
    Nov 2006
    Posts
    10
    Thanks all of you for kind help.
    I will try the solutions.

    BG

    Gostap

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •