How to pull this query?

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi

I have a table with fields, building (text), to (number), from (number).

How do I pull down a record with, let's say the criteria of:

Building= "A" and Between [to] and [from] = 6


Thank you in advance
Richard
 
You could create a calculated field in the query,
eg CalculatedField:Abs([To]-[From]) and make it's criteria 6 and make the
Building field's criteria Like "A"

If [From] is always greater then [To] then you can dispense with the Abs()
function.
 
Do you want something like this:

Where [Building] = 'A' and [to] >= 6 and [from] <= 6
 
Sorry I meant if [To] is always greater than [From] you could dispense with
the Abs() function.

Goober said:
You could create a calculated field in the query,
eg CalculatedField:Abs([To]-[From]) and make it's criteria 6 and make the
Building field's criteria Like "A"

If [From] is always greater then [To] then you can dispense with the Abs()
function.

Richard said:
Hi

I have a table with fields, building (text), to (number), from (number).

How do I pull down a record with, let's say the criteria of:

Building= "A" and Between [to] and [from] = 6


Thank you in advance
Richard
Click to expand...
Click to expand...
 
HI Goober

Thanks for replying. If I use the ABS function, if [from]=5 and [to]=10 the
return is 5. So, if I set the criteria to 6, I won't get any records.

Richard

Goober said:
Sorry I meant if [To] is always greater than [From] you could dispense with
the Abs() function.

Goober said:
You could create a calculated field in the query,
eg CalculatedField:Abs([To]-[From]) and make it's criteria 6 and make the
Building field's criteria Like "A"

If [From] is always greater then [To] then you can dispense with the Abs()
function.

Richard said:
Hi

I have a table with fields, building (text), to (number), from (number).

How do I pull down a record with, let's say the criteria of:

Building= "A" and Between [to] and [from] = 6


Thank you in advance
Richard
Click to expand...
Click to expand...
Click to expand...
 
Hi

I tried this but couldn't pull down any records.

Thanks again
Richard

Squirrel said:
Do you want something like this:

Where [Building] = 'A' and [to] >= 6 and [from] <= 6


Richard said:
Hi

I have a table with fields, building (text), to (number), from (number).

How do I pull down a record with, let's say the criteria of:

Building= "A" and Between [to] and [from] = 6


Thank you in advance
Richard
Click to expand...
Click to expand...
 
Richard said:
Hi

I have a table with fields, building (text), to (number), from (number).

How do I pull down a record with, let's say the criteria of:

Building= "A" and Between [to] and [from] = 6
Click to expand...

I don't understand the use of Between here. Do you want to pull down all
records
where Building is A and both [to] and [from] are 6?
Maybe an example of records you want and records you don't want would help.

Tom Lake
 
Richard,

Well, I agree with Tom... to get a good answer, you need to explain the
question. But anyway, I can't resist having a shot in the dark at this
one. Do you mean this:
Where [Building] = 'A' and [to]<=6 and [from]>=6
 
Hi Tom

The table is for reference. Actually there are more fields in the table...

the [to] and [from] are actully floor numbers. So, I just want to pull down
one record ie. the building with a floor number.

for eg.

Building from to Data
"A" 1 5 A1
"A" 6 10 A2
"B" 1 3 B1
"B" 4 8 B2

Let's say I want to lookup Building A with floor number 6, then the data i
want is A2

Richard




Tom Lake said:
Richard said:
Hi

I have a table with fields, building (text), to (number), from (number).

How do I pull down a record with, let's say the criteria of:

Building= "A" and Between [to] and [from] = 6
Click to expand...

I don't understand the use of Between here. Do you want to pull down all
records
where Building is A and both [to] and [from] are 6?
Maybe an example of records you want and records you don't want would help.

Tom Lake
Click to expand...
 
the [to] and [from] are actully floor numbers. So, I just want to pull
down
one record ie. the building with a floor number.

for eg.

Building from to Data
"A" 1 5 A1
"A" 6 10 A2
"B" 1 3 B1
"B" 4 8 B2

Let's say I want to lookup Building A with floor number 6, then the data i
want is A2
Click to expand...

In a query, make another column, Floor:[Enter Floor Number]

The criterion for Building is

="A"

and the Criterion for Floor is

Between [From] And [To]

When run the query will ask for a floor number then return the Data for that
Floor. Here's the SQL:


SELECT [Building Table].Data
FROM [Building Table]
WHERE ((([Building Table].Building)="A") AND (([Enter Floor Number]) Between
[from] And [to]));

Tom Lake
 
Hi Steve

Tried that but didn't work

Richard

Steve Schapel said:
Richard,

Well, I agree with Tom... to get a good answer, you need to explain the
question. But anyway, I can't resist having a shot in the dark at this
one. Do you mean this:
Where [Building] = 'A' and [to]<=6 and [from]>=6

--
Steve Schapel, Microsoft Access MVP
Hi

I tried this but couldn't pull down any records.

Thanks again
Richard
Click to expand...
Click to expand...
 
Richard,

Thanks for the further explanation. In addition, I see you have also
posted an example, which helps a lot. Well according to the example,
the suggestion made earlier by Squirrel should be applicable, i.e....
Where [Building] = 'A' And [to]>=6 And [from]<=6
So, if this didn't return the expected dataset, then either your example
is wrong, or you must be doing something else wrong.
 
Hi Tom

Works perfectly. Thanks

Richard

Tom Lake said:
the [to] and [from] are actully floor numbers. So, I just want to pull
down
one record ie. the building with a floor number.

for eg.

Building from to Data
"A" 1 5 A1
"A" 6 10 A2
"B" 1 3 B1
"B" 4 8 B2

Let's say I want to lookup Building A with floor number 6, then the data i
want is A2
Click to expand...

In a query, make another column, Floor:[Enter Floor Number]

The criterion for Building is

="A"

and the Criterion for Floor is

Between [From] And [To]

When run the query will ask for a floor number then return the Data for that
Floor. Here's the SQL:


SELECT [Building Table].Data
FROM [Building Table]
WHERE ((([Building Table].Building)="A") AND (([Enter Floor Number]) Between
[from] And [to]));

Tom Lake
Click to expand...
 
Back
Top