Access 2010 - Combine query for specific year and query for between two years

[Anathil]

Hi everyone and thank you in advance.

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
1- FindYearSpecific
2- FindYearStart
3- FindYearEnd

...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...
First of all, thank you, NeoPa, for helping with that code. I found it in an unrelated post.
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 and sorry for the wall of text.

[jforbes]

I think you have two questions listed. We normally limit a thread to one question.
I think the first question is how to perform build a query with multiple criteria and the second is how to reset the Form.

For your where clause, I think your gonna want something like this:

WHERE Year(SomeYear) = [Forms]![frmSearchGeneral].[FindYearSpecific]
  OR ([Forms]![frmSearchGeneral].[FindYearSpecific] IS NULL 
       AND SomeYear 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]
      )

The first part of the OR will return records if the a SpecificDate is selected, where the second part of the OR will only return records if the SpecificDate is Null and there are records found with the Between.


I think for Resetting the Form, you'll want to use:

me.FindYearStart.Value = Null

The reason for this is that in your query, you are checking for Nulls instead of Zero Length Strings. For more information, check this out: What is the difference between "" and Null?

[Anathil]

Sorry for the double question.
In my head it was a question and a weird quirk...

But you solved it :)
It works perfect!

Thank you so much.

And if it can benefit others:
I wrote the code in my query builder omitting the Year(SomeYear) because in the query builder you put the top field (Field) to:

Year([FieldWithDate])

So my final code in the criteria field was:

[Forms]![frmSearchGeneral].[FindYearSpecific]
[Forms]![frmSearchGeneral].[FindYearSpecific] Is Null And 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])

Spread over two criteria fields.

And it works beautifully :)
Thanks again :)