Hi Aexley,
In your initial question, you mentioned trying the "DLookup" function. This caused me to believe you might be working with an Excel database table.
If indeed you might be working with such (or might want to create) an Excel database, you should be aware of the tremendous flexibility of the various database functions, including "DCOUNT". Using this Excel database functionality allows for creating very powerful search "criteria", where you could easily MIX your search criteria to include Employees, Addresses, Ages, etc TOGETHER with Dates.
The three parts of the "Database Formula" are: 1) Database, 2) Offset, and 3) Criteria. While too many Excel users still do NOT use "Range Names", use of these names is HIGHLY recommended - because Excel then automatically takes care of adjusting the coordinates of the range names, as opposed to having to manually modify code or formulas whenever a row or column is inserted.
EXAMPLE Database Formula:
=DCOUNT(Database,Offset,Crit)
...wherein you assign the "Database" range name to your database;
"Offset" - replace this with the Field Name of the column you are performing your DCOUNT on (or DSUM, DMAX etc). Alternatively, you could (if appropriate for the context of your particular application) create a Range Name for the "Offset" portion of the Database formula. Your choice of names is arbitrary ...i.e. you can name is whatever you want ...I have used the name "offset" but you could call it any other name (except for a small number of "reserved" names). Then, in the cell which you have named "offset", enter the field name of the column you wish to perform your DCOUNT or DSUM, etc.
The third portion of the Database Formula is "Crit" (This is short for "Criteria" which is a "reserved" name in Excel - see "NOTE: RESERVED NAMES" further below.) This again is an arbitrary Range Name. Whatever name you use in the database formula, assign that name to the range which contains the field name(s) and row(s) you have created for your set of search criteria. This is where for example you could use "Employee", "Age", etc as additional field names together with the "Date" field.
In the case of the simple MONTH formula which doesn't take into account the YEAR, you need to appreciate that this will ONLY be a valid option IF your database ONLY includes data for ONE year. If this IS the case, then the formula to use (in the row below the "Date" field in your criteria range) would be: =MONTH(Date)=4 for the month of April.
If you wanted to create a "matrix" of formulas for all months, you would then create separate criteria for each of the months. Create unique range names for each of the separate criteria ...example: Crit_Jan, Crit_Feb, Crit_Mar, Crit_Apr, etc. Then simply copy your first database formula and change the last three characters of the range name (for the subsequent months).
In case you are not aware, Excel's database functionality ALSO includes the ability to EXTRACT data to SEPARATE ranges and/or to SEPARATE Sheets.
Here is some VB code which is used to extract data:
Sub Extract_Data()
Application.ScreenUpdating = False
Range("database"
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="crit", _
CopyToRange:=Range("extr"
, _
Unique:=False
Application.ScreenUpdating = False
End Sub
NOTE: RESERVED NAMES: In Excel, as with other software, there are a few "reserved" range names. In this particular area (database formulas), there are two which you should NOT use: "Criteria" and "Extract".
In case you want to create (now or in the future) database formulas which reference a database with MULTIPLE YEARS, you should use this formula in the cell below the "Date" field of your criteria:
=AND(Date>=From,Date<=To) ...where "Date" refers to the field name "Date", "From" refers to a cell named "From", in which you enter the "from date" (e.g. 04/01/2000), and "To" refers to a cell named "To" in which you enter the "to date" (e.g. 04/30/2000).
In creating a "dynamic" application, one should consider creating "dynamic" formulas for the "From" and "To" dates. These formulas would include the Month and Day as "hard code", but reference a separate cell for the YEAR. This way, the end-user would only need to change that one cell which contains the YEAR (when advancing to the next year).
I hope this has been helpful.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
