Using two textboxes to query Between two values. X < X

[SteveFlash]

I am trying to set up a form with two text boxes so I can run a query that searches for records with values between a two numbers. In the query design view I was able to do this by placing the following in the criteria statement of the field titled "Amount":

Between 10 And 50

Because this worked I tried to use the following to have the query reference the two text boxes on my form:



Between [Forms]![Search]![AvgAmtO] And [Forms]![Search]![AvgAmt2]

but this doesn't work. Does anyone one know how to do this or have any advice?

[pbaldy]

It would help to know what "doesn't work" means, as it should. Make sure you've tabbed off the second form control before running the query.

[SteveFlash]

It just brings back result without taking the two values in the text boxes into account. If I was to put say "10 to 20" in the two text boxes on the form and then press the button to lunch the query the results I get will have avg amounts from 1 to 10,000.

What do you mean by tabbed off the second form control in the query?

[SteveFlash]

I am trying to set up a form with two text boxes so I can run a query that searches for records with values between a two numbers. In the query design view I was able to do this by placing the following in the criteria statement of the field titled "Amount":

Between 10 And 50

Because this worked I tried to use the following to have the query reference the two text boxes on my form:

Between [Forms]![Search]![AvgAmtO] And [Forms]![Search]![AvgAmt2]

but when I run the query from the form it brings back people with all AvgAmt ranges. Does anyone know what the proper criteria statement is?

[alansidman]

Your criteria looks to be ok. Are all your objects and controls spelled correctly? Double check for spaces,etc.

[SteveFlash]

The amount field is formatted as a number and I am inputting numbers. I can't for the life of me figure this out and it is so frustrating because I know the solution would take 30 seconds but I have spent hours.

[pbaldy]

I mean to move focus from it. Can you post the db here?

[SteveFlash]

Ummm I can't really because it has some private info. Can this be done by simply adding a command to the criteria of the filed in the query design? Or does it require more work then I realize?

[alansidman]

cross posted at: http://www.access-programmers.co.uk/...d.php?t=240473

Please read this: http://www.excelguru.ca/content.php?184

Take a look at this regarding the between function. http://www.techonthenet.com/sql/between.php

Perhaps you would like to post a copy of your db for analysis and a fresh pair of eyes.

[SteveFlash]

Thanks Alan. I found to forums and decided to post the question in both, yes. I can preform the function no problem if I put in the numbers into it but if I try to reference the text boxes for the numbers that when it does not work. Do you know what might be the problem.

[pbaldy]

I merged your threads; please don't post the same question twice.

[June7]

Paul means you must Tab or Enter from each control after entering the criteria values.