Query type range between to fields/colums

[lazy_scrapper]

Can someone please provide info on how I would write querie to select only values that meet criteria for the following scenario.

Attachement has data examples
one table, 3 columns all text

I need to have all CI displayed where appfrom or appto includes the value of "2" in the range.
The example should display CI1001 and CI1000.

If I changed the value required to 4. I should only return CI1001, CI1000 and CI1003.

Any help for a novice would be exceptional.

 

[Brianwarnock]

You presumeably want to vary the criteria rather than hardcode, therefore use a form , callit queryform, with 2 text boxes valappfrom and valappto and a command button to run the query.

In the first row of the criteria on the design grid under appfrom code
=forms!queryform!valappfrom or forms!queryform!valappfrom is null
similar on same row for valappto

If there is no entry ie null in the textbox all values will be selected.

Brian

 

[Uncle Gizmo]

>>>I need to have all CI displayed where appfrom or appto includes the value of "2" in the range.<<<

The example should display CI1001 and CI1000.

No.... Only CI1001 would be displayed in the example given.....

And the example with "4" don't make sense either....

 

[lazy_scrapper]

Think I did not explain to well.

A range of numbers between appfrom and appto.
so if appfrom = 0 and appto = 6; then the range in between includes 0 and 1 and 2 and 3 and 4 and 5 and 6.

so my original post is 100% correct.

And I still need help

I think it does need to be hardcoded, as the end result will be used as a master table for other needs and queries.

Have searching in the mean time, and was looking at the IIf statement.
Having issues with this also as my fields are text, not number?

 

[lazy_scrapper]

seems i did not explain correctly

i need it to look at the range between appfrom and appto.

if appfrom =0 and appto = 6 then the numbers within this range is 0,1,2,3,4,5,6.
so the results would be as originally posted.

need it to be hardcoded so the query can be used to build on for final results.

Have been looking at the IIf function but the field is text not number? Would this work if field was converted to other format? How?

 

[Brianwarnock]

So if you had numeric values it would be simple. Where does the "A" fit into the scheme of things?

 

[Uncle Gizmo]

>>> so my original post is 100% correct. <<<<

No wrong again....

 

[lazy_scrapper]

Thanks brianwarnock, for providing assistance.

Hardcode below is simple but very effective when identifying ranges of numbers between multiple fields (see example in original attachment).

SELECT ciapp.ci, ciapp.appfrom, ciapp.appto
FROM ciapp
WHERE (((ciapp.appfrom)<="4") AND ((ciapp.appto)>="4"));

-------------------------------------------------------------
To all those that clutter forums. If you dont understand you should not post.

 

[Uncle Gizmo]

>>>To all those that clutter forums. If you don't understand you should not post<<<

Wrong again, when I don’t understand, I point out what I don’t understand to give the poster the the opportunity to clarify the issue.

 

[Kryst51]

So if you had numeric values it would be simple. Where does the "A" fit into the scheme of things?

I agree, I think the text in your columns will cause you issues. unless you account for it.

 

[Brianwarnock]

I agree, I think the text in your columns will cause you issues. unless you account for it.

Looking through it all and at his final solution I would say that the alpha will work ok if he expects alpha > numeric, the only problem would be "numbers" >9, but I expect he knows that.

There was no excuse for him to be rude to an experienced poster like Gizmo who was trying to understand the misleading posts so that he, Gizmo, could help, in fact to any poster who is trying to help.

Brian

 

[Kryst51]

Looking through it all and at his final solution I would say that the alpha will work ok if he expects alpha > numeric, the only problem would be "numbers" >9, but I expect he knows that.

There was no excuse for him to be rude to an experienced poster like Gizmo who was trying to understand the misleading posts so that he, Gizmo, could help, in fact to any poster who is trying to help.

Brian

Yeah, I don't like the rudeness either.....

 

[lazy_scrapper]

proven results, it works even with alpha. It may have issues with +10 (not tested by me). My requirement is one char only. refer mdb or or file, to prove for your self.

 

[TexanInParis]

I would have written the query:

SELECT *
FROM ciapp
WHERE "4" BETWEEN ciapp.appfrom AND ciapp.appto;

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[lazy_scrapper]

SELECT *
FROM ciapp
WHERE "4" BETWEEN ciapp.appfrom AND ciapp.appto;


Thanks for the rewrite, much cleaner.