Sorting Query through Forum for range

[dbalilti]

Hi guys,



So I want to sort my records with a query search forum like the one in this video:

http://www.datapigtechnologies.com/f...earchform.html

I am getting everything to work except for the range fields.

i want to be able to insert a range and get records, but at the same time, if I dont put anything, the form will know to just ignore it and bring back results for the other parameters I inserted.

basicly i want to be able to do something like this:

In the criteria of the query for range field: like between forms!form!min.value and forms!form!max.value and *

what i have now is: Between [forms]![RunQuery]![MinSuite_Sort]="Value" And [forms]![RunQuery]![MaxSuite_Sort]="value"

and when i run it, nothign works.

I hope what I am asking makes sense if not, tell me and i will try and clarify.

thank you.

[orange]

What exactly is the data type of the

[forms]![RunQuery]![MinSuite_Sort]="Value" And [forms]![RunQuery]![MaxSuite_Sort]="value"

If they are numbers, you do not need quotes.
Do you get an error message. If so, then please tell us what it is.

[John_G]

Hi -

Between [forms]![RunQuery]![MinSuite_Sort]="Value" And [forms]![RunQuery]![MaxSuite_Sort]="value"

If this is what your statement really looks like, what it translates to is "between False and False", because [forms]![RunQuery]![MinSuite_Sort]="Value" is either true or false, and the same for [forms]![RunQuery]![MaxSuite_Sort]="value"



Better syntax for your between is :
Between [forms]![RunQuery]![MinSuite_Sort] And [forms]![RunQuery]![MaxSuite_Sort]

if your form fields are numeric, or:

"Between '" & [forms]![RunQuery]![MinSuite_Sort] & "' And '" [forms]![RunQuery]![MaxSuite_Sort] & "'"

if your form fields are text.

However, I would be careful in using this, because it would be very easy to make a mistake in the form, and not get the results you expect, for example if you leave one of the values out, or reverse the From and To.

Access will never "Ignore a parameter.."; it will always use it, so you have to be careful with the between, yhou can't use it like the others; if you leave them both blank, you will get between "" and "" or between 0 and 0, which I suspect will almost always be false.

Maybe you could give a bit more detail about what you need to do.

HTH

John

[dbalilti]

When i run the query, nothing shows up. No error

[Rawb]

Use the Nz() Function to provide a default value. That way, if no value is given, it will use the provided defaults.

Between Nz([forms]![RunQuery]![MinSuite_Sort], #10/12/1492#) And Nz([forms]![RunQuery]![MaxSuite_Sort], Date())

P.S.
+1 internets if you recognize the beginning date!

[dbalilti]

YES! This worked! Thankyou! Problem Solved

EDIT**

Almost works

I have to put values for both ranges for it to work, the Default Values are Not Working.

So when I put nothing I get an error: Run-Time Error '3071'. This epcression is Type Incorrectly, or it is too complex to be evaluated.

Here is what I have: Between Nz([forms]![Advanced Search]![Unit_Min],0) And Nz([forms]![Advanced Search]![Unit_Max],99999)

Its probably some simple fix that will make me feel stupid, but w/e Im very new to access.

[John_G]

Hi -

The error message may be misleading. What is it you are applying the "Between" to? If it is a table field or query field of type numeric integer, try changing the 99999 to 32767, since 32767 is the maximum value for integer type.

John

[dbalilti]

it is a query field that I am applying the Between criteria to. Note, i also changed the Max to 32767 and I am still getting the same error.

[Rawb]

I'm not seeing anything wrong with your Expression...

Try replacing it with the following (remove the Nz() Function) and see if you still get an error:

Between 0 And 32767

If you still get an error, then the problem isn't with that part of the Query.

[dbalilti]

No error when I replace the Criteria with between 0 and 32767

[John_G]

In what context are you using the query? Is the form open when you run the query?

John

[dbalilti]

I use the Form to open the query. So in my form I apply the criteria then I run the Query

[Rawb]

Can we see the actual SQL syntax of that portion of the Query (beginning at "WHERE" and ending at the first "AND" or "OR" after the expression)? That might help us figure out what's happening.

Also, is this a Summation Query or a regular Select Query (if the Sigma icon is selected, it's a Summation Query)?

[dbalilti]

WHERE (((Records.Developer) Like [forms]![Advanced Search].[Developer] & "*") AND ((Records.Region) Like [forms]![Advanced Search].[Region] & "*") AND ((Records.Units) Between Nz([forms]![Advanced Search]![Unit_Min],0) And Nz([forms]![Advanced Search]![Unit_Max],32767)));

and the Totals is not on.

[Rawb]

I'm pretty sure it actually has more to do with the Records.Developer and Records.Region conditions than Records.Units...

Let's say I enter the following values in your Form:
- Developer = Rawb
- Region = US of A
- Unit_Min = 5
- Unit_Max = 100

The Query would end up looking like this:
WHERE (((Records.Developer) Like Rawb*) AND ((Records.Region) Like US of A*) AND ((Records.Units) Between Nz(5,0) And Nz(100,32767)));

As you can see, the two "Like" expressions have quotes incorrectly applied (or not applied at all in this case). I think that's what's causing your error. I just don't know why it would work without the Nz() Functions.

What we're trying to get it to look like is (with single quotes around "Rawb*" and "US of A*"):
WHERE (((Records.Developer) Like 'Rawb*') AND ((Records.Region) Like 'US of A*') AND ((Records.Units) Between Nz(5,0) And Nz(100,32767)));

Depending on how you're getting the expressions into your Query though, that could be hard...

Do you specify [forms]![Advanced Search].[Developer] in the Query itself or are you passing that in through VBA?