Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Question Unanswered: Need to Loop save first and last matches

    I have a table created by someone ele. The 2 fields I'll need is mtrFlow a number field and datetime field a timestamp field. The table records every minute of the day and if the mtrflow is being used it will have a number in the field (any number) in the mrtflow. If it is not in use it will show zero in the mtrflow field. Now I need to save the Timestamp value for when the mtrflow is in use and not in use. example: 08/01/08 08:00:00 am till 08/01/08 10:00:00 am the mtrflow has a number 99 showing it in use. At 08/01/08 10:01:00 am the mtrflow is zero indicating not in use till say 08/01/08 11:30:00 am then it has the number 99 again. This will happen all day long. So I want to save only the 08/01/08 8:00 am time (Start)and only the 08/01/08 10:00 am (end)in a temp table to later subtract the 2 datetimes and come up with 2 hours and so on total each days useage. Could somone please help me with the code. I've been using cut and paste and it takes a long time. I'd like the start and end to go on the same record so I can subtract them each time. Be happy to elaborate if needed. Thanks!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I would create a DAO recordset of your current table and then loop through the DAO recordset testing for whatever boolean criteria you think you need. Let us know if you need more help, but that should be a start I would think.
    Me.Geek = True

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums by the way
    Me.Geek = True

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The easiest way to do this should be using the DMin and DMax functions:

    StartTime = DMin("<The column name here>", "<Table or Recordset name here>", "<Criteria here>")

    with <column name> = "TimeStamp",
    <table name> = the name of the table where the values are stored,
    <criteria> should be something like:
    "TimeStamp (Between #01/01/2009# AND #01/10/2009#) AND (mrtflow <> 0)"

    If you want to perform more complex selections, you should consider using a Recordset based on SQL, like this:

    Dim rst as DAO.Recordset
    Dim strSQL as String
    Dim TimeMin as Date
    Dim TimeMax as Date
    '
    ' This will give you the Min. value
    '
    strSQL = "SELECT TOP 1 TimeStamp, mrtflow FROM <table> WHERE TimeStamp <your for TimeStamp conditions here> AND <Other conditions here...> ORDER BY TimeStamp ASC"
    Set rst = Currentdb.OpenRecordset(strSQL)
    TimeMin = rst!TimeStamp
    rst.Close
    '
    ' This will give you the Max. value
    '
    strSQL = "SELECT TOP 1 TimeStamp, mrtflow FROM <table> WHERE TimeStamp <your for TimeStamp conditions here> AND <Other conditions here...> ORDER BY TimeStamp DESC"
    Set rst = Currentdb.OpenRecordset(strSQL)
    TimeMAX = rst!TimeStamp
    rst.Close

    Have a nice day!

  5. #5
    Join Date
    Feb 2009
    Posts
    4
    Quote Originally Posted by nckdryr
    I would create a DAO recordset of your current table and then loop through the DAO recordset testing for whatever boolean criteria you think you need. Let us know if you need more help, but that should be a start I would think.
    Awesome link. Will give this a try and see if this does the trick. Might have to ask for more help once I create the code. Thanks so much for replying...ps what is ID ten errors?

  6. #6
    Join Date
    Feb 2009
    Posts
    4
    Quote Originally Posted by Sinndho
    The easiest way to do this should be using the DMin and DMax functions:

    StartTime = DMin("<The column name here>", "<Table or Recordset name here>", "<Criteria here>")

    with <column name> = "TimeStamp",
    <table name> = the name of the table where the values are stored,
    <criteria> should be something like:
    "TimeStamp (Between #01/01/2009# AND #01/10/2009#) AND (mrtflow <> 0)"

    If you want to perform more complex selections, you should consider using a Recordset based on SQL, like this:

    Dim rst as DAO.Recordset
    Dim strSQL as String
    Dim TimeMin as Date
    Dim TimeMax as Date
    '
    ' This will give you the Min. value
    '
    strSQL = "SELECT TOP 1 TimeStamp, mrtflow FROM <table> WHERE TimeStamp <your for TimeStamp conditions here> AND <Other conditions here...> ORDER BY TimeStamp ASC"
    Set rst = Currentdb.OpenRecordset(strSQL)
    TimeMin = rst!TimeStamp
    rst.Close
    '
    ' This will give you the Max. value
    '
    strSQL = "SELECT TOP 1 TimeStamp, mrtflow FROM <table> WHERE TimeStamp <your for TimeStamp conditions here> AND <Other conditions here...> ORDER BY TimeStamp DESC"
    Set rst = Currentdb.OpenRecordset(strSQL)
    TimeMAX = rst!TimeStamp
    rst.Close

    Have a nice day!
    Thanks going to copy code and see how this works...might need more help afterwards but this is the direction I needed.

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by pattyd
    ps what is ID ten errors?
    I.D. ten T error = ID10T error
    Me.Geek = True

  8. #8
    Join Date
    Feb 2009
    Posts
    4

    Talking U got me good!

    I had not heard of that b 4 and u can bet I won't forget it! Thanks for the funny I needed it! Smile cause I am.

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    PS,
    That wasn't meant to be at you, it's just my signature
    Me.Geek = True

Posting Permissions

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