Filter records between two numbers IF a number is present

[NightSpy2]

Hi, I have two text boxes and I'm wondering how I can filter my records of my table (through my query), between two numbers on my form.
So for example lets call the two text boxes Num1 and Num2, the logical process is:

IF Num1 or Num2 IS empty
THEN filter records with "*"
ELSE IF Num1 and Num2 contain a number
THEN filter records between those two numbers

Someone please help! I've tried next to everything!
SQL, Me.Filter, BETWEEN in criteria. I have no idea how I can accomplish this.

If you can help me with this it would be VERY much appreciated!

 

[CJ_London]

Try this code - you'll need to change names to suit your actual requirements

SELECT * FROM YourTable WHERE iif(isnull(Yourform.form.num1) or isnull(Yourform.form.num2), true, YourTable.num between Yourform.form.num1 and Yourform.form.num2)=true

 

[NightSpy2]

Ok I put it in as in my SQL code under the WHERE:

... AND ((iif(isnull([Forms]![SearchForm]![MovieYear1]) or isnull([Forms]![SearchForm]![MovieYear2]), true, [MovieList].num between [Forms]![SearchForm]![MovieYear1] and [Forms]![SearchForm]![MovieYear2])=true)) AND ...

And when I try to run the Query it prompts me to 'Enter a parameter value' for MovieList.num

My SELECT and FROM code is as follows (but I think there's no problem with this bit):

SELECT MovieList.[Movie ID], MovieList.Name, MovieList.Rating, MovieList.Genre, MovieList.MovieYear, MovieList.Director, MovieList.Length, MovieList.Status
FROM MovieList

 

[CJ_London]

you need to replace movielist.num with the name of the field in movielist which needs to be between the forms movieyear values - looks like it should be movielist.movieyear.

Also recommend changing your name field in movielist to say moviename. Reason is name is a reserved word and can cause unexpected problems in the future

 

[Keith Tedbury]

Hi, if you go into design view on the query and enter this code into a new column

IIf([forms]![nameofform]![nameofbox]="" Or IsNull([forms]![nameofform]![nameofbox]),
[nameoftable].[nameoftablefield] Like "*" Or [nameoftable].[nameoftable] 
Is Null,[nameoftable].[nameoftablefield]>=[forms]![nameofform]![nameofbox])

In the Criteria put <>false and change the Total to where

This piece of code is for the from part just copy this code into another column and change the >= to <= for the to

Probably not as elegant as the solution CJ shows.

 

[NightSpy2]

Thanks for you help. I've got a solution now.
I used the code:

WHERE Between [Forms]![SearchForm]![MovieYear1] 
      And [Forms]![SearchForm]![MovieYear2]
      OR [Forms]![SearchForm]![MovieYear1] Is Null

and it seems to work perfectly. Do you recommend I change it to yours, or does this perform the same function?

 

[CJ_London]

what happens when [Forms]![SearchForm]![MovieYear1] is null?

 

[NightSpy2]

It shows all records
Exactly what I needed