Unanswered: Two text boxes in a form to query values in Between X to Y
Hi. 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. I have set up 2 text boxes in the form design, they are named AvgAmt0 and AvgAmt2.
In the query design view I was able to produce the desired results 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, when I run the query I got the whole range from the Amount field from 1 to 10,000. Does anyone know how to do this?
I have had things like this happen from time to time. It depends on how the underlying table that you are querying is defined. Something that you can try to see if it helps is to cast the Text boxes into the data type that you need. Something like "Between CInt([Forms]![Search]![AvgAmtO]) And CInt([Forms]![Search]![AvgAmt2])". This would make sure that it is searching between to Integers and not text.