Ok, now that you have the year you can use that to filter records in a query.
The form must remain open to use the value of the combobox.
In the query under the Payment Date field (I am making an assumption here that
this is the field you want to filter by.
Field: JustYear: Year[Payment Date]
Criteria: Forms![NameOfForm]![NameOfCombobox]
That will not be quite as efficient as a more complex expression but unless
you have many tens of thousands of records you probably won't notice the
performance hit. If you do have a LOT of records then this criteria will be
much more efficient. It will be very efficient if you have an index on the
Payment Date field.
Field: Year[Payment Date]
Criteria: >= DateSerial(Forms![NameOfForm]![NameOfCombobox],1,1)
AND > DateSerial(Forms![NameOfForm]![NameOfCombobox] +1,1,1)
An easy way to handle return all years if nothing is selected would be to use
the latter criteria with the following.
Criteria: >= DateSerial(Nz(Forms![NameOfForm]![NameOfCombobox],1900),1,1)
AND > DateSerial(NZ(Forms![NameOfForm]![NameOfCombobox],2999)+1,1,1)
If payment date could be blank and you wanted all the records returned then
life gets even more complex.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I am a novice and appreciate the help. The changes you gave me do bring up a
drop down box with the years I am looking for but if I select a year it does
not seem to change anything. I want to click on one of the years on the list
and have it only show records for that year and have it also only show that
year in my reports. If I don't pick a year I would like it to show all years
as it does now. Do I need to put this code somewhere else. I added it to my
main form. The database I borrowed the code from seems to only have it on the
main form but maybe there is other code I am missing.
John Spencer said:
Is it possible that Membership Puchase should read Membership Purchase?
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
When I try this I get this message:
The record source 'SELECT DISTINCT Year([PaymentDate]) AS PaymentYear FROM
[Membership Puchase] ORDER BY Year([PaymentDate]);' specified on this form or
report does not exist.
I originally copied the code from a different database and changed the table
and field names. Do I need to set something else up?
:
Since your table name has spaces in it, you must surround the name with square
brackets. Good naming practice for field and table names is to name with only
letters, numbers, and the underscore character. Also you need to avoid
reserved words such as Year, Date, Name, Time for field names.
Your query for the row source should probably look like the following:
SELECT DISTINCT Year([PaymentDate]) AS PaymentYear
FROM [Membership Puchase]
ORDER BY Year([PaymentDate]);
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
ArneGolf wrote:
I would like to pull records by year. I was hoping to change the year on my
main page form and then pull records from that. I would like my reports etc.
to only pull records from whatever year I use. What I tried gave me an error.
I tried adding to the row source:
SELECT DISTINCT Year([PaymentDate]) AS PaymentYear FROM Membership Puchase
ORDER BY Year([PaymentDate]);
Membership Purchase is my table and PaymentYear is my field that I would
like to use to determine the year. Is there something wrong with this or is
there a better way to do this?
.
.
