Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012

    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++)
    if (dateList[i]=currentDate)

    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.


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    A query will do and it will be more efficient:
    Sub SearchForNow()
        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)
        With rst
            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.
                ' or:
                varArray = .GetRows(100)    ' Either the first 100 rows or the whole set if it has less than 101 rows.
            End If
        End With
        Set rst = Nothing
    End Sub
    Last edited by Sinndho; 07-01-12 at 03:39.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts