I am working on a user form where you as part of the filtering process can enter a single year or an interval between two years.
I have three unbound fields
...and I am using the query builder cause I need to query other fields as well, so I would like to keep doing that instead of writing VBA (but that might be a mistake, I don't know...)
To find the specific year I have the following code: Like[Forms]![frmSearchGeneral].[FindYearSpecific] & "*"
This works just fine and give me all the records if I don't specify a year. This is exactly what I want
Then I have the following code for the year range: Between IIf([Forms]![frmSearchGeneral].[FindYearStart] Is Null,#01-01-1000#,[Forms]![frmSearchGeneral].[FindYearStart]) And IIf([Forms]![frmSearchGeneral].[FindYearEnd] Is Null,#31-12-9999#,[Forms]![frmSearchGeneral].[FindYearEnd])
A little explanation seems in order cause this is long...
In my top field in the query builder (Field) I have - FindYears: Year ([Date])
I use the extreme dates to make sure that I get all records if the field in the user form is left empty.
This works just fine most of the time. And I am not sure why it is only most of the time.
I have to write in both the fields before the code execute correctly. After that when I manually clears the fields everything works a cording to plan but if I use code to clear the fields like: me.FindYearStart = "" I don't get any records when I run my query.
And on top of that, my real problem is, that I seem unable to combine the two criteria to get anything useful :S
Just to sum up:
I need a query that do the following
1- Checks if I am asking for a specific year
if Yes, then return all records for that year - Then nothing else
if No, continue to 2
2- Checks if I am asking for a year range
If FindYearStart is empty, then returns all records form the beginning of time until year entered in FindYearEnd.
If FindYearEnd is empty, then returns all records form FindYearStart to the end of time.
If there is a year in both fields, then returns the range asked for
I think I am almost there but I really need some help to get the rest of the way.
Thank you in advance