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.