Forum Discussion

PeterEarle's avatar
Qrew Member
3 years ago

Search in a range

Hiya, I have a table with a number ranges connected to a customer, and I need to be able to search and return an answer if a number is identified within that range.   For example, a customer has the phone number ranges between  8907000 and 89070100, meaning they use all the numbers between 8907000 and 89070100.  If I do a search on the number (ie) 89070050 I would expect to see this customer data come up.    Can anyone explain how I do this.  cheers .

Peter Earle

1 Reply

  • Thereat least two solutions.

    You can set up an <ask the user. report where the user will enter the number twice.  

    All of 
    where [Range Start] is less than or equal to <ask the user>
    where [Range End] is greater than or equal to <ask the user>

    Trying part for that for the user is they have to answer the question twice with the same answer being the number they are searching for.
    The other method is to set up a helper table with one record in it. It will be record ID number one. Make a relationship back to your details table with a reference field of a formula of `1. Make a field on that single record where you can enter the number to be search. Look it up into your details table and then make a formula which checkboxes to true if the number is within the range of numbers. Then on that single record table form you will put a list of all detail records but use the report that is filtered where the checkbox is true.
    The nice thing from a user point is they simply enter the number onto that dedicated search form and when they save the results will be right on the form and they only have to enter the number once. 


    Mark Shnier (YQC)