Select query to get unique year from tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I haven't much luck in finding an answer to this in the community. I have
three related tables, Companies, Agreements, and Filings in an ACCESS 2002
SP3 database. I've modified one set of forms/subforms so instead of showing
all of the Filings for every year and other unneeded info, it just shows the
company identifiers, agreement data and part of the filings data. I know how
to create a subquery using the BETWEEN function (BETWEEN [begin_date] AND
[end_date] as an example). What I haven't been able to do is create a
subquery that will pull only the Filings for one specific year. I'd like to
set it up so when I select form / report from the SWITCHBOARD a pop-up form
appears asking for the year requested.
As an example, the pop-up would appear with a text box into which I could
enter 2005. When I click on a COMMAND BUTTON, the query is run returning
every company and it's agreements that were filed in 2005. It would also be
nice to be able to change the query parameters to return those which did NOT
file in a specific year, but that's probably another story for another
session. Unless someone is really feeling kind and helpful. I hope my
intentions are clear, if not honorable.
Thank you.
 
The main form button code would be similar to this (I'm not sure if you have
a fiscal year field or just a date, I've assumed a date field):

btnOpenForm_Click()
Dim strFY As String
Dim strWhere as String

strFY = InputBox("Enter a YEAR:", "Year", Year(Date))
'defaults to current year - optional in code

If (strFY = "") Then
strWhere = "[Date Field] = Null"
Else
strWhere = "Year([Date Field]) = " & strFY
End If

DoCmd.OpenForm "My Form", , , strWhere
End Sub
 
I don't know if a pop up is going to do it for you unless it stays open while
the query is running. All you need to do is be sure what ever form the query
will get its value from stays open. Queries can't use memory variables, so
you have provide them a place to get the value. Let's say we have a form
named frmBuzFoo and a text box on it named txtFilingYear. In the criteria
row for the year field in your table, you can tell it what year like this:

[Forms]![frmBuzFoo]![txtFilingYear]

Now it will include only those years.

One thing you did not clarify is what data type the field you want to filter
on is. The above will work for text or numeric. If it is a date data type,
you will need to create a calculated field in your query that will return
only the year. For example, in the Field Row of your query:

FilingYear: Year([FilingDate])
Then apply the criteria above to this field.

It is definitly possible to exclude a year, but that gets in to modifying
SQL in VBA, so lets start with this.
 
Instead of using a pop-up, how about if you add a combo box to the form that
will display the data. In fact, if you add one combo box to pick Company
and a second to pick Year, your form's source could be a query that looks at
the two combo boxes for search criteria. You'd add code to the AfterUpdate
events of the combo boxes to requery the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for all the good advice. Using a single combo box for year is all I'd
need as I want to see everyone who filed. The pop-up form was an idea from
several apps we have at work that have pop-ups asking for the criteria needed
for a report.
--
I know enuff to be dangerous.


Jeff Boyce said:
Instead of using a pop-up, how about if you add a combo box to the form that
will display the data. In fact, if you add one combo box to pick Company
and a second to pick Year, your form's source could be a query that looks at
the two combo boxes for search criteria. You'd add code to the AfterUpdate
events of the combo boxes to requery the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP


RealGomer said:
I haven't much luck in finding an answer to this in the community. I have
three related tables, Companies, Agreements, and Filings in an ACCESS 2002
SP3 database. I've modified one set of forms/subforms so instead of
showing
all of the Filings for every year and other unneeded info, it just shows
the
company identifiers, agreement data and part of the filings data. I know
how
to create a subquery using the BETWEEN function (BETWEEN [begin_date] AND
[end_date] as an example). What I haven't been able to do is create a
subquery that will pull only the Filings for one specific year. I'd like
to
set it up so when I select form / report from the SWITCHBOARD a pop-up
form
appears asking for the year requested.
As an example, the pop-up would appear with a text box into which I could
enter 2005. When I click on a COMMAND BUTTON, the query is run returning
every company and it's agreements that were filed in 2005. It would also
be
nice to be able to change the query parameters to return those which did
NOT
file in a specific year, but that's probably another story for another
session. Unless someone is really feeling kind and helpful. I hope my
intentions are clear, if not honorable.
Thank you.
Click to expand...
Click to expand...
 
Back
Top