Access Table that has a Low and High Number Question

[jfg863]

I have a access link that is to a server table. The table has a column that has a low number and then a separate column that has a high number. For example, the low could be 100 and the high could be 200. This means that any account that falls in between those 2 numbers pass the test of this table. So If I had an account that had an account number of 101 and I put that in my form and click the run button it should bring back any row that has both fields as 101 (so low and high is both 101, meaning this is the only account that this rule applies to) and any other row that has say low of 100 and high of 200 because its in between both of those. I have a few columns like this, account low and high, function low and high.

How would I make my query look in both columns like this? Hopefully I explained this clear enough. I appreciate all of your help. I will be giving back some to these forums soon because I do have some good knowledge on the easier questions.

These fields could be text fields or number fields

[hapm]

The BETWEEN operator is what you want. SELECT Low, High FROM YourTable WHERE [YourValueFromTheForm] BETWEEN Low AND High

[jfg863]

So far in the criteria area for these fields I have Like [Forms]![ValidationForm1_3]![AccountCMBO] & "*" in every field so that if I keep a field blank in the form it will continue to give me the results based on just one of the fields on the form being populated. If I put just what you said as for example one of the fields: Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH] it works by itself. But if I put that same thing in the other field that is on this form too and I leave that field blank and I just search for account in the form it will bring back no results now. If I try to put these together it says it's too complex, I've also tried:
Like "*" And Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH] & "*" and also tried Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH] & "*" and for the other field the same things in conjunction with each other and neither will product data together

Also Tried:
Like [Forms]![ValidationForm1_3]![AccountCMBO] & "*" And Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH]
with the same thing in the other form field:
Like [Forms]![ValidationForm1_3]![FunctionR3CMBO] & "*" And Between [OP_TYP] And [OP_TYP_HIGH]
And also tried putting an "Or" in between them instead of an "And"



Any fix for this?

The other form I started for the field being blank problem is at: https://www.accessforums.net/access/...ion-44947.html

[jfg863]

Here's some additional information if it may help someone to help me out:

With: Like [Forms]![ValidationForm1_3]![Textbox1] & "*" in every field that I am using on my form it allows me to keep fields blank and only search on certain fields I want (June7 helped me with this at https://www.accessforums.net/access/a...ion-44947.html). Now that I'm almost done with this database, I've found one more problem I need to take into account.

This is that there are columns that have low and high range of numbers for each field. For example as stated above, there may be an account that is between the range that also needs to come up in the query if it is searched.
Example:
I put in the textbox account 708000 and I keep the other textbox blank. In my access query 21 records come back, only providing the rules where the low and high columns are 708000 but it skips the other 44 records that have a range of 100001 in the low column and 999999 in the high column. How could I get it to include these and also allow for blank fields in the form?