When you pull up the field definitions for this table, is this field of type number, text or date? (a date field can be formatted to display just the year.)
Has this worked before and now doesn't?
It is a number field, not a date field. This is the 2nd year that I am using the database and so I have never needed to search by date in the past.
The 2 tables (festival and films) are connected by the festival ID. So when I select the date in "films" it connects to the record in "festivals" with the corresponding ID. I assume that something about the Festival ID number must be the problem as all other searches seem to be working, including number searches (i.e., I can search by the year the film was made with no problems.)
So you have two tables linked like this?
Festivals::FestivalID = FilmTitles::FestivalID
And this date is a field in Festivals?
Are you performing this find on the festivals layout or the filmTitles layout? (I'd assume the filmTitles, but let's be sure.)
I'm wondering how you manage cases where the same film is shown in more than one festival...
yes to the tables linking and to searching in FilmTitles
A film is only submitted to 1 festival (this is all for the same festival, the Festivals table just keeps track of the dates, locations etc for each year. We wouldn't show a film one year that had been shown the year before) and so the year indicates which festival year they have submitted their film/fee/etc to.
In which case, if you place the Year field from Festivals on the FilmTitles layout, you should be able to enter find mode, then enter or select a year and your find should bring up all FilmTitle records that are related to a Festival record with that year.
Does that match what you are trying to do?
If so, make sure that the field where you are selecting/entering a year is the year field from Festivals, not a field defined in FilmTitles.
the year the film is made is not necessarily the same as the festival year. So they are two different fields for different info, if I am understanding your question correctly. thanks though
yes, but which year are you searching on? The festival year or the year it was made?
I've assumed you were searching for the festival year.
yes I am searching for the festival year. But as a test I searched by year made and it worked fine, so I don't think it has to do with being a number field. It seems to be just this one particular field (which is connected to another table) that is the problem.
If you enter criteria into the production year field you find records of titles produced in that year. If you search on the festival year, you find records for titles shown in a festival that year. They are two different fields so success finding by production year doesn't really tell us anything about the searching by festival year. It would help to confirm that the Festival year field is a number field.
Either should work, but that relationship may be at issue.
Describe exactly what you are doing when you perform your find. Are you sure that you are entering the year in the correct field (festival::year)?
(When you enter layout mode while on the FilmTitles field, you should see a field name that has this format: ::FieldName to show that it comes from the related Festivals table.)
If a thread can't be resolved in three or four responses, it usually suggests that we review the file. If you can, please upload the file. If you don't have a site, there are free ones such as 4shared.com. Then provide a link to it in a response here ... along with telling us which layout to go to and which field you are searching. Also be sure that there is at least one record which should produce a match (this isn't necessary but it helps).
I even created 'your file' in an attempt to understand the issue and, like Phil, we are still unclear. :^)