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:
[code=vb]Set qdf = CurrentDb.Creat eQueryDef("", "SELECT T1.Increment FROM Table1 AS T1 WHERE T1.Value1=(SELE CT Min(T2.Value1) FROM Table1 AS T2 WHERE T2.Value1>=[Value];);")[/code]

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.

[code=vb]Public Function AccessVLookup(s trTable As String, strLookupField As String, _
varLookupValue As Variant, strReturnField As String, _
Optional strCriteriaFiel d As String, Optional varCriteriaValu e 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.
'dblExchangeRat e = AccessVLookup(" tblCurrencyExch angeRate", "EffectiveDate" , Date, "ExchangeRa te", "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(strCriteria Field) > 0 Then
strSQL = strSQL & " AND [" & strCriteriaFiel d & "]"
strSQL = strSQL & " = " & varCriteriaValu e
End If

strSQL = strSQL & ")"

Set qdf = CurrentDb.Creat eQueryDef("", strSQL)
qdf.Parameters( "LookupValu e") = varLookupValue

Set rst = qdf.OpenRecords et
If rst.RecordCount > 0 Then AccessVLookup = rst.Fields(strR eturnField)

rst.Close
qdf.Close

Set rst = Nothing
Set qdf = Nothing

End Function[/code]

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.