CODEWITHSUNDEEP
ms-accessvbams-access-2003
RCoy1978
RCoy1978

Reputation: 125

Lookup and Display a Date Value from an Access Table

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

Upvotes: 1

Views: 4627

Answers (2)

HK1
HK1

Reputation: 12230

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.

Upvotes: 1

StuckAtWork
StuckAtWork

Reputation: 1633

I think what you're looking for is the following:

'Dates to be retrieved from the db (quarter start dates)
Dim q1 As Date
Dim q2 As Date
Dim q3 As Date
Dim q4 As Date
'Today's date
Dim today As Date
Dim quarter As Integer

Set today = Date()
Set q1 = DLookup("FieldContainingDate", "tableContainingDates", "quarter=1")
Set q2 = DLookup("FieldContainingDate", "tableContainingDates", "quarter=2")
Set q3 = DLookup("FieldContainingDate", "tableContainingDates", "quarter=3")
Set q4 = DLookup("FieldContainingDate", "tableContainingDates", "quarter=4")

Set quarter = 1 'Base case.
If (today > q1) Then quarter = 2
If (today > q2) Then quarter = 3
If (today > q3) Then quarter = 4

MsgBox "It is quarter " & quarter 'Display which quarter it is in a msgbox

You may have to fiddle with the date formatting depending on how you have it stored in the database, etc. It would also be much more efficient to write it in another way (for instance remove the intermediary q# variables) but I wrote it out in a lengthy way to make it more clear.

Upvotes: 0

Related Questions