none
Expression Query To compare cell value (Ms. Access) RRS feed

  • Question


  • How to make an Expression Query to compare cell value of each cell from field "CHECKTIME" as above picture

    I need functions on expression as bellow:

    - Value from "RecordNo 2" will compare with value "RecordNo 1"
    - If value "RecordNo 2" less than value "RecordNo 1" result will "FAIL" otherwaise will "PASS"
    - The next record value will compare with above record which has value "PASS"
    - Comparison is group by SENSORID as foreign key in this table

    Thanks in advance for help



    Monday, February 26, 2018 8:42 AM

Answers

  • Helloo Deden,

    I would suggest you custom a function and then call the function in query to get the column.

    Here is the example function code. Put it in a standard module.

    Public Function CustomFunction(SENSORID As Integer, CHECKTIME As Date)
    currentTime = CHECKTIME
    SQL = "Select * from TestTable where SENSORID=" & SENSORID
    
    Dim db As Database
    Set db = CurrentDb
    Dim rs As Recordset
    Set rs = db.OpenRecordset(SQL)
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            If rs!CHECKTIME > max Then
            max = rs!CHECKTIME
            End If
            
            If rs!CHECKTIME = currentTime Then
                If currentTime >= max Then
                CustomFunction = "Pass"
                Else
                CustomFunction = "Fail"
                End If
                rs.Close
                Set rs = Nothing
                Exit Function
            End If
            rs.MoveNext
        Loop
    Else
        MsgBox "There are no records in the recordset."
    End If
    End Function
    

    And then call the function in Query.

    SELECT  [RecordNo],[SENSORID],[CHECKTIME],  CustomFunction([SENSORID],[CHECKTIME]) as PassOrFail from TestTable;

    Result

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 2, 2018 9:31 AM

All replies

  • Why do you want FAIL for RecordNos 3, 7 and 11? 6:41 is later than 6:05...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, February 26, 2018 9:45 AM
  • Hello Hans, Thanks for your response,

    It is to compare with previous value which has PASS result in expression,
    RecordNo 3 will compare with RecordNo 1, because record 2 is FAIL
    RecordNo 7 will compare with RecordNo 5,  because record 4 is FAIL
    RecordNo 11 will compare with RecordNo 9, because record 8 is FAIL

    Thanks in advance and appreciate for this help

    Regards,

    Deden



    Tuesday, February 27, 2018 12:59 AM
  • Helloo Deden,

    I would suggest you custom a function and then call the function in query to get the column.

    Here is the example function code. Put it in a standard module.

    Public Function CustomFunction(SENSORID As Integer, CHECKTIME As Date)
    currentTime = CHECKTIME
    SQL = "Select * from TestTable where SENSORID=" & SENSORID
    
    Dim db As Database
    Set db = CurrentDb
    Dim rs As Recordset
    Set rs = db.OpenRecordset(SQL)
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            If rs!CHECKTIME > max Then
            max = rs!CHECKTIME
            End If
            
            If rs!CHECKTIME = currentTime Then
                If currentTime >= max Then
                CustomFunction = "Pass"
                Else
                CustomFunction = "Fail"
                End If
                rs.Close
                Set rs = Nothing
                Exit Function
            End If
            rs.MoveNext
        Loop
    Else
        MsgBox "There are no records in the recordset."
    End If
    End Function
    

    And then call the function in Query.

    SELECT  [RecordNo],[SENSORID],[CHECKTIME],  CustomFunction([SENSORID],[CHECKTIME]) as PassOrFail from TestTable;

    Result

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 2, 2018 9:31 AM
  • Hello,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 5, 2018 8:34 AM
  • Hello Terry,

    It Works,

    Thanks a lot, I appreciated for this help

    Best Regards,

    Deden

    Tuesday, March 6, 2018 2:32 AM