Find and view a date value from an access table

advertisements

I am trying to have a msgbox popup when clicking on a command button within a form in Access 2003.
The msgbox should be triggered by the current date, when compared to dates referenced within a table that is in the database. It would look like this:

If Date() is < [Date in table?], THEN "Msgbox" = "It is now Quarter 2"

once it is beyond the date for quarter 3, the msg box would read "It is now Quarter 3"

Thanks if you can help


Access has a set of functions called Domain Functions for looking up a single piece of information stored in a table. Some of the most common ones are DCount(), DLookup(), DSum(), DAvg(), DMax(), and DMin().

You need to use the DLookup function for this. Basically, it needs a field name and a table name to lookup a value. And in many cases you want to include a criteria statement (or WHERE clause) as the third argument to make sure that the DLookup function is actually retrieving the value from the correct row. If you don't pass in a criteria statment, the Domain functions will simply return the first match.

If Date() <= DLookup("SomeDateField", "tblYourTableName") Then
    MsgBox "The date in stored in the table is today or else is in the future."
Else
    MsgBox "The date stored in the table is in the past."
End If

Here's an alternate way to write this:

If Date() < DLookup("SomeDateField", "tblYourTableName") Then
    MsgBox "The date in stored in the table is in the future."
Else
    MsgBox "The date stored in the table is today or is in the past."
End If

And here's how you do it if you have multiple records/rows in the table. You then need to some kind of criteria statement to narrow it down to retrieving the value you want to from the very row you want.

If Date() < DLookup("SomeDateField", "tblYourTableName", "UserID = 1") Then
    MsgBox "The date in stored in the table is in the future."
Else
    MsgBox "The date stored in the table is today or is in the past."
End If

While it's not really what you are asking, I think it's important to realize what's really going on behind the scenes with this function (and other domain functions). Basically, you are choosing to retrieve one single value from one single table with the option to specify which record/row you want the value retrieved from using a criteria statement, known as a WHERE clause in SQL. So let's take a look at how you would write a function like this, and at how Microsoft likely did write their DLookup function.

Public Function MyLookup(ByVal strField As String, _
                         ByVal strTable As String, _
                         Optional ByVal strCriteria As String) As Variant
    'Error handling intentionally omitted
    'Proper error handling is very critical in
    'production code in a function such as this
    If strField <> "" And strTable <> "" Then
        Dim sSQL as string
        sSQL = "SELECT TOP 1 " & strField & " FROM " & strTable
        If strCriteria <> "" Then
            sSQL = sSQL & " WHERE " & strCriteria
        End If
        Dim rst As DAO.Recordset
        Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
        If Not (rst.EOF and rst.BOF) Then
            MyLookup = rst(strField).Value
        End If
        rst.Close
        Set rst = Nothing
    End If
End Function

Now let's suppose you want to find the birthdate of someone in your contacts table:

Dim dteBirthDate as Date
dteBirthDate = MyLookup("BirthDate", "tblContacts", "ContactID = " & 12345)

If you didn't have a DLookup function, (or if you didn't write your own), you'd end up writing all that code in the "MyLookup" function up above for every time you needed to lookup a single value in a table.