Solved Find approximate date (1 Viewer)

[zelarra821]

Hello. I have a table where I keep the days on which a given event occurred.

What I want is to search for a specific date (I have done this), and, if it does not coincide with any of the saved dates, search for the closest previous one.

For example:

4-jan-22
6-jan-22
9-jan-22
13-jan-22

If I search for January 9th, I want it to be positioned in the record for January 9th. However, if I search for January 8th, I want it to position on January 6th.

I want to do it with bookmarks.

This is the code I have

Sub ChooseDate(FName As Form)

Dim first As DAO.Recordset
Dim Date As String

Date = Format(InputBox("Choose the date you want to search for.", DBName), "mm/dd/yyyy")

If StrPtr(Date) = vbEmpty Then Exit Sub
If Date = "" Then Exit Sub

Set rst = FName.Recordset
rst.FindFirst "Date=#" & Date & "#"
If Not first.NoMatch Then
     FName.Bookmark = rst.Bookmark
else
     rst.FindPrevious "Date=#" & Date & "#"
     If Not first.NoMatch Then
         FName.Bookmark = rst.Bookmark
     else
         MsgBox "There are no records for that date.", vbInformation, DBName
     End If
End If
Set first = Nothing
End Sub

Thank you.

 

[bastanu]

Sorry but the code you have makes no sense, (like the FName.Recordset). I suggest you study some more the use of DAO recordsets and refine your code. To get what you need (the exact date or the closest to it) you would simply use:
=DMax("[YourDateField]","[tblYourTable]","[YourDateField] <=#" & YourSearchDateVariable & "#")

Cheers,

 

[zelarra821]

Ok, thanks. The code is based on an example that I got in Microsoft Help. I have to change recordset to recordset lone and put your code in the criteria.

 

[arnelgp]

your code will work on "Ordered" (sorted on date) recordset.
modified version:

Sub ChooseDate(FName As Form)

    Dim rst As DAO.Recordset
    Dim sDate As String
    Dim sTable As String
    Dim sReturn As String
    
    sDate = Format(InputBox("Choose the date you want to search for.", DBName), "mm/dd/yyyy")
    
    If Len(sDate) = 0 Then Exit Sub
    
    Set rst = FName.RecordsetClone
    
    ' arnelgp
    ' get the Source table/query of the field (Date)
    '
    sTable = rst.Fields("Date").SourceTable
    
    ' check if we can get at least a record
    ' from the table
    With CurrentDb.OpenRecordset( _
                    "select [Date] from [" & sTable & "] " & _
                    "where [Date] <= #" & sDate & "# " & _
                    "order by [date] desc;", dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveFirst
            ' get the date equal or less than sDate
            sReturn = Format$(![Date], "mm/dd/yyyy")
        End If
    End With
    If Len(sReturn) <> 0 Then
        With rst
            .FindFirst "Date=#" & sReturn & "#"
            FName.Bookmark = .Bookmark
        End With
    End If
    Set rst = Nothing
End Sub

 

[zelarra821]

Thanks, @arnelgp. It works for me.