Form filter Between in two instances, to still work without values?

[NightSpy2]

:banghead:

^That's exactly how I feel right now.

The thing I've been trying to do is make it so that my form filters my records, and I'm trying to make a between function for it. My form is shown below in the attachment.

What I need to do is make it so that my form filters my records Between the two year boxes AND between the two Length boxes. But I need it so that if nothing is in the boxes, it shows all records, and if something is in only the Year boxes, it only filters the years and not the lengths.
It's been really hard because I used the code:

Field: Length
Criteria: Between [Forms]![SearchForm]![Length1] And [Forms]![SearchForm]![Length2]

Then in a separate column I had

Field: [Forms]![SearchForm]![Length1] 
Or: Is Null

This works fine if it's only for Length, but if I try to do the same for the MovieYear boxes, it screws up and just shows me no records...?

I really have no idea how to make them both work! Any help would be greatly appreciated!

 

[CJ_London]

can you post your sql for the query - it looks like it is probable a simple case of sorting out ands and ors

 

[John Big Booty]

You could use the Nz() to return a value below you expected minimum for the From criteria and above the expected maximum for the to criteria.

 

[John Big Booty]

So for example;

Between Nz([Forms]![SearchForm]![YearField], 1900) And Nz([Forms]![SearchForm]![YearField], 3000)

 

[NightSpy2]

This is all of my SQL:

SELECT MovieList.[Movie ID], MovieList.Name, MovieList.Rating, MovieList.Genre, MovieList.MovieYear, MovieList.Director, MovieList.Length, MovieList.Status
FROM MovieList
WHERE (((MovieList.Name) Like "*" & [Forms]![SearchForm]![Name] & "*") AND ((MovieList.Rating) Like "*" & [Forms]![SearchForm]![Rating] & "*") AND ((MovieList.Genre) Like "*" & [Forms]![SearchForm]![MovieGenre] & "*") AND ((MovieList.MovieYear) Between [Forms]![SearchForm]![MovieYear1] And [Forms]![SearchForm]![MovieYear2]) AND ((MovieList.Director) Like "*" & [Forms]![SearchForm]![Director] & "*") AND ((MovieList.Status) Like "*" & [Forms]![SearchForm]![MovieStatus] & "*")) OR ((([Forms]![SearchForm]![MovieYear1]) Is Null))
ORDER BY MovieList.[Movie ID];

It's working now because I only have code for the MovieYear, not for length. If I have both it doesn't work..

 

[Pat Hartman]

The "like" solution only works for text fields. It won't work for numeric or date fields. Here's a method that works regardless of the data type and which will be more efficient since it won't preclude the use of indexes.

Where (fldA = Forms!yourform!FldA OR Forms!yourform!FldA Is Null)
AND (fldB = Forms!yourform!FldB OR Forms!yourform!FldB Is Null)
AND (fldC Between Forms!yourform!FldC AND Forms!yourform!FldD OR Forms!yourform!FldC Is Null)
AND ....

Notice that each condition is enclosed in parentheses. This is critical since the where clause contains both AND and OR operators and you need to control the way the expression is evaluated.

 

[NightSpy2]

The "like" solution only works for text fields. It won't work for numeric or date fields. Here's a method that works regardless of the data type and which will be more efficient since it won't preclude the use of indexes.

But I need LIKE for my Name/Rating/Genre etc boxes? As in so that they don't need to type in the full name for it to work. Will that still happen with this code? Because it looks like '=' so the text has to be exact, am I right?

I need it to show records even if I don't enter anything into some boxes.

 

[Pat Hartman]

I believe I said that you can use Like with text fields. It may prevent the query engine from using an index to satisfy the query but that will only be a problem if you have a huge recordset.

I said, you can't use Like with non-text fields. I can't tell what data type your field is so I offered an alternative.

 

[NightSpy2]

I believe I said that you can use Like with text fields. It may prevent the query engine from using an index to satisfy the query but that will only be a problem if you have a huge recordset.

I said, you can't use Like with non-text fields. I can't tell what data type your field is so I offered an alternative.

Yes I know that you said that, but in the code that you showed me it has an '=' operator, so what I was wondering is that if I used your code, would I need to type exact values in order for the records to be filtered. Does that make sense?

 

[NightSpy2]

OH MY GOSH! I finally did it! Thanks everyone for your help.
I tried writing my own SQL and it finally worked!
I'll post it up later on when I can!