Query Design to Filter datas "in between"

[nizam]

Hi friends,
I need a help to design a query to filter datas (not dates) in between two ranges. For Eg:-
I have 10 line numbers;
Line No1
Line No2
Line No3
.
.
.
Line No10.

Each Line No is have 100 Joint Nos;
Eg:- Line No1, J#01,J#02.......J#100
Line No2, J#01,J#02.......J#100
etc

I need to make a Query to filter datas of " each line number" from a particular J# to another J# (Continuous ascending only)



Please advise me how to make expression / criteria for the design of this query...

Thanks in advance
Nizam,

[June7]

Are J#01, J#02, etc fields? Or are these values in a field? What is that field name?

[nizam]

Are J#01, J#02, etc fields? Or are these values in a field? What is that field name?

The J# 01,J#02 etc are in the same filed names "Joint Nos"

Eg as shown,

Line No Joint No

Line#1 J#01
Line#1 J#02
Line#1 J#03
...
...
.
.
Line#2 J#01
Line#2 J#02
Line#2 J#o3
..
..
..
Line#10 J#01
Line#10 J#02

etc ..
Means a Line No is having Many joint numbers..

I need to make query to check the status of the Joint numbers, in a particular line number,
say eg:- i need to check the status of J#10 to J#25 in Line#1 or any other line number..
I know the way to ask [Line No] when we run the query, but i need to ask joint No also from which joint to which joint..

plz advise

[June7]

That would be simple if the number part of the value was always the same length. Example J#001, J#002, ... J#100. This is alpha sorting and your structure of the value will not sort J#100 properly. Either correct the data or have to extract the number part with expression in query and filter on that constructed field. Example:
Val(Mid([Joint No],3)) Between 7 And 22

[nizam]

Plz make it clear little more, what i have to put the expression, i need the ask when running the query, first no and the last number for the specified line no,
plz

[June7]

Put expression in cell of Field row in query design.
Val(Mid([Joint No],3))

Then in the Criteria row
Between value1 And value2

[nizam]

No chance boss, when try to run, the error message coming as
"you tried to execute a query that does not include the specified expression 'Val(Mid([Joint No],3)) between value1 and value2 as part of an aggregate function.

[June7]

Post your SQL statement. I expected you would do a simple SELECT, not a totals (Group By).

[nizam]

Dear I Solved the Issue.. Thank god,,
it is so simple....
just in the criteria column, i put 2 criterias, > and < ,, then it will ask the criteria when it runs and it will give the exact required..
Thanks a lot for you kind support.. looking ahead