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
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
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.