Queryed for 30 & Higher Returns 3's & 4's

[Rob]

My query is simple or at least I thought it was but I keep getting bad results.

The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...

=[Qty Needed].[Enter Number]

All I want is to prompt for a number and have the query show everything that
is equal to or greater than that supplied number. I get prompted and it
performs the search but the results are not what I am expecting.

For instance - If I ask for 30 or higher I'll get a bunch of three's and
fours and fives as well as 30+, 40+, & 50+'s.

How do I keep the query from returning that result?

Thanks in Advance.
Rob

 

[Jerry Whittle]

Sounds like the Qty Needed field is a Text data type. It needs to be a number
data type for your criteria to work.

I don't understand this part:

=[Qty Needed].[Enter Number]

Please go to SQL view of the query and post the entire SQL statement. If Qty
Needed is a text field, there might be a way of still extracting the
numerical characters as numbers and getting the query to work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

My query is simple or at least I thought it was but I keep getting bad results.

The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...

=[Qty Needed].[Enter Number]

All I want is to prompt for a number and have the query show everything that
is equal to or greater than that supplied number. I get prompted and it
performs the search but the results are not what I am expecting.

For instance - If I ask for 30 or higher I'll get a bunch of three's and
fours and fives as well as 30+, 40+, & 50+'s.

How do I keep the query from returning that result?

Thanks in Advance.
Rob

 

[KARL DEWEY]

Try it this way --
The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...

=[Enter Number]

--
Build a little, test a little.

My query is simple or at least I thought it was but I keep getting bad results.

The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...

=[Qty Needed].[Enter Number]

All I want is to prompt for a number and have the query show everything that
is equal to or greater than that supplied number. I get prompted and it
performs the search but the results are not what I am expecting.

For instance - If I ask for 30 or higher I'll get a bunch of three's and
fours and fives as well as 30+, 40+, & 50+'s.

How do I keep the query from returning that result?

Thanks in Advance.
Rob

 

[Rob]

In the Criteria Row of the Design Window for my Query I have theat as the
criteria.

=[Qty Needed].[Enter Number]...

~ Greater Than

= ~ Equal to
[Qty Needed] ~ Table Field/Column to Search within
[Enter Number] ~ The Prompt to get the "search for" number

Sounds like the Qty Needed field is a Text data type. It needs to be a number
data type for your criteria to work.

I don't understand this part:

=[Qty Needed].[Enter Number]

Please go to SQL view of the query and post the entire SQL statement. If Qty
Needed is a text field, there might be a way of still extracting the
numerical characters as numbers and getting the query to work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

My query is simple or at least I thought it was but I keep getting bad results.

The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...

=[Qty Needed].[Enter Number]

All I want is to prompt for a number and have the query show everything that
is equal to or greater than that supplied number. I get prompted and it
performs the search but the results are not what I am expecting.

For instance - If I ask for 30 or higher I'll get a bunch of three's and
fours and fives as well as 30+, 40+, & 50+'s.

How do I keep the query from returning that result?

Thanks in Advance.
Rob

 

[Rob]

Thanks Very much for the suggestion... Unfortuantely I still get all those
extras that I am not wanting. In using your example I also tried -- Not
<[Enter Number] -- and still received a bunch of 3's, 4's, etc...

Thanks Again.

Try it this way --
The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...

=[Enter Number]

--
Build a little, test a little.

My query is simple or at least I thought it was but I keep getting bad results.

The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...

=[Qty Needed].[Enter Number]

All I want is to prompt for a number and have the query show everything that
is equal to or greater than that supplied number. I get prompted and it
performs the search but the results are not what I am expecting.

For instance - If I ask for 30 or higher I'll get a bunch of three's and
fours and fives as well as 30+, 40+, & 50+'s.

How do I keep the query from returning that result?

Thanks in Advance.
Rob

 

[Jerry Whittle]

I'm still confused. Please post the entire SQL statement for that query.
Open the query in design view. Next go to View, SQL View and copy and past it
here.

Also check that field's data type at table design mode. I'm thinking that
it's Text.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In the Criteria Row of the Design Window for my Query I have theat as the
criteria.

=[Qty Needed].[Enter Number]...

~ Greater Than

= ~ Equal to
[Qty Needed] ~ Table Field/Column to Search within
[Enter Number] ~ The Prompt to get the "search for" number

Sounds like the Qty Needed field is a Text data type. It needs to be a number
data type for your criteria to work.

I don't understand this part:

=[Qty Needed].[Enter Number]

Please go to SQL view of the query and post the entire SQL statement. If Qty
Needed is a text field, there might be a way of still extracting the
numerical characters as numbers and getting the query to work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

My query is simple or at least I thought it was but I keep getting bad results.

The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...
=[Qty Needed].[Enter Number]

All I want is to prompt for a number and have the query show everything that
is equal to or greater than that supplied number. I get prompted and it
performs the search but the results are not what I am expecting.

For instance - If I ask for 30 or higher I'll get a bunch of three's and
fours and fives as well as 30+, 40+, & 50+'s.

How do I keep the query from returning that result?

Thanks in Advance.
Rob

 

[Petr Danes]

It sounds like you're using text fields for numeric values. Try your
criteria like this:

instead of the test >=[Qty Needed].[Enter Number]

use >=Val([Qty Needed].[Enter Number]) and up in the window where you
specify the field, say FLD, put VL: Val(FLD)

When you use text data types for numbers, this is a common problem, since
text string are evaluated left to right in comparisons.

Pete

Thanks Very much for the suggestion... Unfortuantely I still get all those
extras that I am not wanting. In using your example I also tried -- Not
<[Enter Number] -- and still received a bunch of 3's, 4's, etc...

Thanks Again.

Try it this way --
The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...

=[Enter Number]

--
Build a little, test a little.

My query is simple or at least I thought it was but I keep getting bad
results.

The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...
=[Qty Needed].[Enter Number]

All I want is to prompt for a number and have the query show everything
that
is equal to or greater than that supplied number. I get prompted and
it
performs the search but the results are not what I am expecting.

For instance - If I ask for 30 or higher I'll get a bunch of three's
and
fours and fives as well as 30+, 40+, & 50+'s.

How do I keep the query from returning that result?

Thanks in Advance.
Rob

 

[KARL DEWEY]

Placing [Qty Needed].[Enter Number] in criteria is wrong syntax as [Qty
Needed] is the field being tested.
What is needed due to the text field is this --
The Table is... Ordering Info
The Field is... Checking: Val([Qty Needed])
The Criteria Used is...

=[Enter Number]

--
Build a little, test a little.

It sounds like you're using text fields for numeric values. Try your
criteria like this:

instead of the test >=[Qty Needed].[Enter Number]

use >=Val([Qty Needed].[Enter Number]) and up in the window where you
specify the field, say FLD, put VL: Val(FLD)

When you use text data types for numbers, this is a common problem, since
text string are evaluated left to right in comparisons.

Pete

Thanks Very much for the suggestion... Unfortuantely I still get all those
extras that I am not wanting. In using your example I also tried -- Not
<[Enter Number] -- and still received a bunch of 3's, 4's, etc...

Thanks Again.

Try it this way --
The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...
=[Enter Number]

--
Build a little, test a little.


:

My query is simple or at least I thought it was but I keep getting bad
results.

The Table is... Ordering Info
The Field is... Qty Needed
The Criteria Used is...
=[Qty Needed].[Enter Number]

All I want is to prompt for a number and have the query show everything
that
is equal to or greater than that supplied number. I get prompted and
it
performs the search but the results are not what I am expecting.

For instance - If I ask for 30 or higher I'll get a bunch of three's
and
fours and fives as well as 30+, 40+, & 50+'s.

How do I keep the query from returning that result?

Thanks in Advance.
Rob

.