Retrieve Records by Date

[Janna]

My underlying table has a field called Date. In my main form, I would like
to have several buttons called 2005, 2006, 2007 and 2008. If, for example, a
user clicks on the 2007 button, I would like a form to open with all the
records from 1/1/2007 to 12/31/07, and so forth with the appropriate year.
What code might I use on the click event to accomplish this? Thanks in
advance.

Janna

 

[Stuart McCall]

My underlying table has a field called Date. In my main form, I would
like
to have several buttons called 2005, 2006, 2007 and 2008. If, for example,
a
user clicks on the 2007 button, I would like a form to open with all the
records from 1/1/2007 to 12/31/07, and so forth with the appropriate year.
What code might I use on the click event to accomplish this? Thanks in
advance.

Janna

First off, you need to change the field name. Date is a reserved word in
Access and should be avoided. I prefer to prefix the word with a subject
name, like 'OrderDate' for instance.

When you use the DoCmd.OpenForm command to open your form, pass the criteria
appropriate to the button clicked in the WhereCondition argument, something
like this:

Dim w As String

w = "Between #1/1/" & ButtonName & _
"# And #12/31/" & ButtonName & "#"
DoCmd.OpenForm "MyForm", WhereCondition:=w

(substituting the actual name of your button in place of ButtonName)

 

[Stuart McCall]

w = "Between #1/1/" & ButtonName & _

"# And #12/31/" & ButtonName & "#"

I missed something out, sorry. That should read:

w = "[OrderDate] Between #1/1/" & ButtonName & _
"# And #12/31/" & ButtonName & "#"

Replace [OrderDate] with whatever you renamed your field to.

 

[Janna]

That works. Thanks much!

w = "Between #1/1/" & ButtonName & _
"# And #12/31/" & ButtonName & "#"

I missed something out, sorry. That should read:

w = "[OrderDate] Between #1/1/" & ButtonName & _
"# And #12/31/" & ButtonName & "#"

Replace [OrderDate] with whatever you renamed your field to.