Unanswered: Text box - formula to test against values for what to display
I have a text box and what I hope to accomplish is test if a value (the current date) is equal to any of the value in a table and if it is equal to a value in the table then display the associated value with that value. So to be more clear. I have a text box. I want to test the current date off of a table that contains a list of dates and an associated number relating to that date. I want to display this number. I have accomplished this using a query subform; however as I am going to have multiple instances differing slightly I would rather take the text box route if possible.
As i am more familiar with C to help explain written in C it would be:
for(i=0; i<dateListEnd; i++)
I am just starting in VBA and not very familiar with it. I know I will need an iff statement (using Date() and Month()) to compare the two and the return value will be the associated value of whichever date matches.
So again, the structure that I am looking for is something like this:
for all entries in the table if date is equal to current date display associated value. There will only ever be one value in the table that is equal.
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM [SomeTable] WHERE [SomeTable].[SomeDate] = Date();"
' If you feel adventurous and want to avoid the problems
' linked to the Time part of a Date, you can use:
strSQL = "SELECT * FROM [SomeTable] WHERE (Clng(Nz([SomeTable].[SomeDate], 0) = CLng(Date())));"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If .EOF Then
MsgBox "No match"
' Use the contents of the Recordset.
' - You can use a loop:
Do Until .EOF
' do something with the contents of the Recordset.
' - Or you can load an array from it:
Dim varArray As Variant
varArray = .GetRows ' only the first row.
varArray = .GetRows(100) ' Either the first 100 rows or the whole set if it has less than 101 rows.
Set rst = Nothing