CODEWITHSUNDEEP
excelvbams-access
Gedeon
Gedeon

Reputation: 44

Get the last date before selected date from ACCESS in Excel

I have an Access DATABASE with date column in table, the dates are sorted but there can be missing dates. The problem is to get the latest date before the input date. For example I managed to solve this problem in excel sheet with just this {=MAX(IF(A1:A10<=C1,A1:A10,0))} formula, but I have a DataBase with large amount of data and need to call it from DB with formula.

I tried to write a code, but it doesn't work. I don't get why ? Could anyone help me with this problem ? thanks.

Public Function GetDateDB(TBL As String, COLMN As String, DT As String) As Variant

Dim DB As Database
Dim RS As Recordset
Dim DBfile

DBfile = Application.ThisWorkbook.Path & "\myDatabase.accdb"

Set DB = DBEngine.OpenDatabase(DBfile)
Set RS = DB.OpenRecordset("SELECT MAX(" & COLMN & ") as MaxDate FROM " & TBL & " WHERE MaxDate <= #" & Format(DT, "m\/d\/yyyy") & "# ", dbOpenDynaset)

If RS.RecordCount > 0 Then
    RS.MoveFirst
    GetDateDB = RS!MaxDate
End If

End Function

Upvotes: 1

Views: 888

Answers (1)

Dorian
Dorian

Reputation: 445

As far as i consider, you only need to replace the ... WHERE MaxDate <= ... by ... WHERE MaxDate < .... (Just delete the equals-symbol)

When you search with <= you will in most cases get the input date.

Upvotes: 1

Related Questions