访问-获取最新日期(如果列中包含另一值) [英] Access - Get latest date if column contains value from another one

问题描述

在Access中查询时，VBA功能出现问题.
我有一个表格" tbldata "

I'm having an issue with a VBA function for my query in Access.
I have a table "tbldata"

Equipment   Last Inspection
420-413     2019-06-15
440-433     2019-06-15
402-483     2019-06-29
420-413     2019-12-12

和查询" qryunpair "

UnpairEquipment
420 
413 
440 
433 
402 
483 

我想要实现的是:

Equipment Latest Date
420       2019-12-12
413       2019-12-12
440       2019-06-15
433       2019-06-15
402       2019-06-29
483       2019-06-29

我编写了以下代码，但是当我运行它时，它没有返回任何值. 有解决办法吗?

I've made the following code, but when I ran it, it didn't return any values. Is there a solution to this?

Function typeinspection(Source As String) As String
Dim Rst As Recordset
Dim Rst2 As Recordset
Dim s As String
    s = ""
    Set Rst = CurrentDb.OpenRecordset("tbldata")
    Set Rst2 = CurrentDb.OpenRecordset("qryunpair")
    While Not Rst.EOF

        If InStr(Source, Rst2.Fields("UnpairEquipment") > 0) Then _
            s = Rst.Fields("Last Inspection")
        Rst.MoveNext
    Wend

    Set Rst = Nothing
    Set Rst2 = Nothing
    typeinspection = s

End Function

推荐答案

如果您希望查询返回期望的结果，则可以使用运算符LIKE将表和查询连接起来:

If you want a query that returns your expected results, you can join the table and the query with the use of the operator LIKE:

SELECT q.UnpairEquipment AS Equipment, MAX([Last Inspection]) AS [Latest Date]
FROM qryunpair AS q INNER JOIN tbldata AS t
ON '-' + t.Equipment + '-' LIKE '*-' +  q.UnpairEquipment + '-*'
GROUP BY q.UnpairEquipment

结果:

Equipment   Latest Date
402         2019-06-29
413         2019-12-12
420         2019-12-12
433         2019-06-15
440         2019-06-15
483         2019-06-29

这篇关于访问-获取最新日期(如果列中包含另一值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！