vlookup for date?

[aas4mis]

I have a form with a FSC field that has a percentage value. I'm wanting to fill that value with data from a "lookup" table. I'm aware of how to get this done in excell but am having problems with access. I've been searching for a while and come across querydef's which have only confused me more. My lookup table has dates in one field and values such as ".1", ".12", ".15" in the other.

I found an example of:

Set qdf = CurrentDb.CreateQueryDef("", "SELECT T1.Increment FROM Table1 AS T1 WHERE T1.Value1=(SELECT Min(T2.Value1) FROM Table1 AS T2 WHERE T2.Value1>=[Value];);")

I can get this to work with double data type, but can't figure out how to lookup by date. Any help would be appreciated.

[janders468]

What's in your lookup table are you wanting to look up a percent by date?

[aas4mis]

My lookup table is two fields, Date and Percentage. Percentage could be written as decimal. .1 for 10% etc.., Yes. I would like to return a percentage by date range.

[aas4mis]

I found a code snippet from Aaron Ringer that solved my problem. It was basically the same I had but in a different layout. It was easier to debug with his code.

Public Function AccessVLookup(strTable As String, strLookupField As String, _
          varLookupValue As Variant, strReturnField As String, _
          Optional strCriteriaField As String, Optional varCriteriaValue As Variant) As Variant
      'Aaron Ringer 26 Feb 08
      'Simulates the Excel VLookup function in Access, complete with Range Lookup argument.
      'I've used it to look up currency exchange rates, to get the XRate active at the given date.
      'It means that there doesn't have to be an entry on the given date, it'll find the next lowest value.
      'Can be used to look up anything, as long as the lookup field contains numeric data.
      'Returns zero if nothing found.
      'strTable = Name of lookup table.
      'strLookupField = Name of field to search.
      'varLookupValue = Value to look for in lookup field.
      'strReturnField = Field to return value from.
      'Example:
      'Gets the exchange rate for the given currency active as of today.
      'dblExchangeRate = AccessVLookup("tblCurrencyExchangeRate", "EffectiveDate", Date, "ExchangeRate", "CurrencyID", lngCurrencyID)
 
        Dim strSQL As String
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
 
        strSQL = "SELECT "
        strSQL = strSQL & "T1." & strReturnField
        strSQL = strSQL & " FROM " & strTable & " AS T1 "
        strSQL = strSQL & "WHERE T1." & strLookupField & "="
        strSQL = strSQL & "(SELECT Max(T2." & strLookupField & ") "
        strSQL = strSQL & "FROM " & strTable & " AS T2 "
        strSQL = strSQL & "WHERE T2." & strLookupField & " <= "
        strSQL = strSQL & "[LookupValue]"
 
        If Len(strCriteriaField) > 0 Then
          strSQL = strSQL & " AND [" & strCriteriaField & "]"
          strSQL = strSQL & " = " & varCriteriaValue
        End If
 
        strSQL = strSQL & ")"
 
        Set qdf = CurrentDb.CreateQueryDef("", strSQL)
        qdf.Parameters("LookupValue") = varLookupValue
 
        Set rst = qdf.OpenRecordset
        If rst.RecordCount > 0 Then AccessVLookup = rst.Fields(strReturnField)
 
        rst.Close
        qdf.Close
 
        Set rst = Nothing
        Set qdf = Nothing
 
      End Function

Another issue I had with my previous code wasn't in the code at all. It was where I was getting my date from. Access won't let you assign a value from a disabled field. I had to copy the field and make it invisible in order to use it. Hopefully this helps somebody.