Retrieve Records by Date

  • Thread starter Thread starter Janna
  • Start date Start date
J

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
 
Janna said:
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
Click to expand...

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)
 
w = "Between #1/1/" & ButtonName & _
"# And #12/31/" & ButtonName & "#"
Click to expand...

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.
 
That works. Thanks much!

Stuart McCall said:
w = "Between #1/1/" & ButtonName & _
"# And #12/31/" & ButtonName & "#"
Click to expand...

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.
Click to expand...
 
Back
Top