Solved Find the first date backwards (1 Viewer)

[zelarra821]

REFORMULATED QUESTION: see my post below. I put it in a simpler way and with a very simple database.

Hello.

I need help finding an amount in a table by date.

I have a table with the subsidies that a farmer receives, where he writes down each year what they are paying him (TIncome).

There is another table where you write down the amount of subsidies (TImporte) for each year.

The amount is calculated by multiplying the amount of the TImporte obtained by the rights (TDerechos), which are the hectares of the property that is entitled to the subsidy.

My problem arises when establishing the criteria to search for the amount by date and by property in TImportes; and the criteria to search for rights by date and farm.

I am giving you a database with data, to see if someone can guide me on how to get it. I suppose I'll have to look for the first one back from the date the income was recorded, but I have no idea.

Also, I would like it to be something that doesn't require a lot of resources, because the only thing I could think of would be a record set, but I wouldn't know how to do it either.

Thank you so much.

 

[jdraw]

???? I do not understand your database or requirement.

From your post, best guess is it seems your set up is generally along this:

 

[zelarra821]

The scheme would be like this.

Be careful that in a year there may be some type of subsidy that is paid more than once.

 

[zelarra821]

I'm going to remove the previous database because I see that it is too complex for something so simple.

I have made a simpler example and in English.

The question, which is also in the AfterUpdate event of the Date field of the only form, is as follows:

How can I achieve through VBA that, when writing the date, it looks for the price that was on that day?

 

[Josef P.]

Select Top 1 Price
from PricesByDate
where DatePrice <= [DateToCheck]
Order By DatePrice DESC

 

[zelarra821]

Solved. Here's the database with the solution that Josef P. gave to me.

 

[Josef P.]

Note: I don't question why you store this data extra in the table. I assume that this is necessary.

Code from example file (#6):

Private Sub DateSale_AfterUpdate()
Dim rst As DAO.Recordset
Dim strSQL As String

    strSQL = "Select Top 1 Price" _
            & " FROM PricesByDate" _
            & " WHERE DatePrice <= #" & Format(Me.DateSale, "mm/dd/yyyy") & "#" _
            & " ORDER BY DatePrice DESC"

    Set rst = CurrentDb.OpenRecordset(strSQL)

    If Not (rst.EOF And rst.BOF) Then
         strSQL = rst("Price") ' <--- ??? strSQL is the variable for the price value?
    End If

    rst.Close

    Set rst = Nothing

    Me.Price = strSQL
End Sub

A little refactoring:

Private Sub DateSale_AfterUpdate()
    SetPrice
End Sub

Private Sub SetPrice()

    With Me.DateSale
        If Not IsNull(.Value) Then
            Me.Price = GetPriceForDate(.Value)
        Else
            ' ???
        End If
    End With

End Sub

Private Function GetPriceForDate(ByVal DateToCheck As Date) As Double
' Why is Double and not Currency or Decimal used for the price in the table?

    Dim strSQL As String
    Dim PriceValue As Variant

    ' You could also consider using a stored parameter query for this.
    strSQL = "Select Top 1 Price" _
            & " FROM PricesByDate" _
            & " WHERE DatePrice <= " & ConvertDateToSqlText(DateToCheck) _
            & " ORDER BY DatePrice DESC"

    PriceValue = LookupSql(strSQL)
    If IsNull(PriceValue) Then
        err.Raise ...
    End If

    GetPriceForDate = PriceValue

End Function

'----------------------------------------------------
' Use this functions in a standard codemodule:

Public Function ConvertDateToSqlText(ByVal Date2Convert As Date) As String
    ConvertDateToSqlText = Format(Date2Convert, "\#mm\/dd\/yyyy\#")
End Function

Public Function LookupSql(ByVal SqlText As String) As Variant

' Note: CurrentDb usage could still be optimized

    Dim ReturnValue As Variant

    With CurrentDb.OpenRecordset(SqlText, dbForwardOnly)
        If Not .EOF Then
            ReturnValue = .Fields(0).Value
        Else
            ReturnValue = Null
        End If
        .Close
    End With

    LookupSql = ReturnValue

End Function

 

[zelarra821]

Thanks