Filter Form by "Date"?

[andmunn]

Hello All,

I have a Database to keep track of certain metrics. Here's some background (copied and pasted from another forum for another question):

I have 6 tables:

tblDivision (is the table of all 31 divisions)
>pkDivID
>txtDivName

tblPerson (contains the names of 25 people - both manger and reporting officers in that division).
>pkPersonID
>txtFirstName
>txtLastname

tblMetricCategory (contains the "categories" each individual metric belong to).
>pkMetricCategory
>CategoryName

tblMetric (contains the various metrics)
>pkMetricID
>fkMetricCategoryID
>MetricName

tblDivisionPerson (states which person belong to which division).
>pkDivisionPersonID
>fkDivisionID
>fkPersonID
>DateEffective
>IsManager

>tblMonthlyResults (is the "heart" of the databas- keeps track of the monthly tallies - by looking at the Div ID / Metric ID / Date - i want to be able to record the metric in a form).
>pkFigures
>fkDivID
>fkMetricID
>MetricDate (i select the first of every month as the default date - metrics collected once a month)
>MetricValue

I have a "main" form with two dropdown (comboboxes) which i want to use to filter another form by.

My First Combo: "WhatUnit"
SecondCombo: "WhatDate"
Then i have a command button (AccessScorecard) to access the form (frmUnitScorecard).

I've figured out how to isolate according to division, and my "onclick" process for the command button (AccessScorecard) reads:

Private Sub AccessScorecard_Click()
Dim strWhereCondition As String
    strWhereCondition = "[pkDivID] = " & WhatUnit
    DoCmd.OpenForm "frmUnitScorecard", , , strWhereCondition
End Sub

The above works just great! I now want to filter by date (i.e// December 1st, January 1st, etc). I want to have a dropdown which says "December, January, etC", so that when i select the division, and the month, it filter's out the appropriate division (this is already working) and the appropriate date.

Any avice?

Thanks
Andrew.

 

[Rabbie]

The Dateserial command may help you. especially if you can pick up the month number from your drop down box

 

[andmunn]

HI Rabbie,

I looked at the date serial command, and i played with a few things, however, i can't see how it would help me.

What i want is to have a drop-down with the various months, and when i choose the "month", it will filter the form based only apon that division selected (which i have working), and the month selected (i.e.// if i choose november, it will only show items that i added on Nov 1, 2009).

Thanks,
Andrew.