Using a comparison operator in a parameter query

  • Thread starter Thread starter Kayla7509
  • Start date Start date
K

Kayla7509

I'm somewhat new to access, and I'm trying to set up a parameter query. I
have tried Criteria: > [Enter Number], but that doesn't work. Can anyone
help me out?
 
It returns all records, rather than using the threshold

Jerry Whittle said:
What do you mean by "doesn't work"? Error message? Wrong records returned?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kayla7509 said:
I'm somewhat new to access, and I'm trying to set up a parameter query. I
have tried Criteria: > [Enter Number], but that doesn't work. Can anyone
help me out?
Click to expand...
Click to expand...
 
Do you have a text field by chance that you are applying the criteria?
If you enter 10000000 at the prompt it will returem 2, 3, 4, 500, etc. as 2
and the rest are greater than 1.
--
KARL DEWEY
Build a little - Test a little


Kayla7509 said:
It returns all records, rather than using the threshold

Jerry Whittle said:
What do you mean by "doesn't work"? Error message? Wrong records returned?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kayla7509 said:
I'm somewhat new to access, and I'm trying to set up a parameter query. I
have tried Criteria: > [Enter Number], but that doesn't work. Can anyone
help me out?
Click to expand...
Click to expand...
Click to expand...
 
The most common problem I have run into with parameter queries is that the
parameter has not been "declared" and Access incorrectly interprets the
response as the wrong data type. Have you actually declared the parameter?
If you have you will see a line similar to the following at the beginning of
the SQL view of your query:

PARAMETERS [Enter Number] Long;

If not, right click in the upper portion of the query grid. Select the
Parameters option, then enter your parameter name [Enter Number] on the left
side of the query Parameters dialog box. Select the appropriate data type
from the right side on that row, and click OK.

Once you have done that, run your query again. Did it work properly?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Kayla7509 said:
It returns all records, rather than using the threshold

Jerry Whittle said:
What do you mean by "doesn't work"? Error message? Wrong records returned?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kayla7509 said:
I'm somewhat new to access, and I'm trying to set up a parameter query. I
have tried Criteria: > [Enter Number], but that doesn't work. Can anyone
help me out?
Click to expand...
Click to expand...
Click to expand...
 
Kayla7509 said:
It returns all records, rather than using the threshold

Jerry Whittle said:
What do you mean by "doesn't work"? Error message? Wrong records
returned? --
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kayla7509 said:
I'm somewhat new to access, and I'm trying to set up a parameter
query. I have tried Criteria: > [Enter Number], but that
doesn't work. Can anyone help me out?
Click to expand...
Click to expand...
Click to expand...

Instead of making everyone guess, I suggest you show us the sql
statement (switch your query to SQL View using the View menu, or the
right-click context menu, or the toolbar button to see the sql
statement), tell us the datatype of the field you are trying to filter,
show us a few values from the field, and tell us what value you are
entering when you are prompted.
 
SELECT qryJoin_GIS.MeterID, qryJoin_GIS.MeterName,
qryJoin_GIS.BalanceSegment, qryJoin_GIS.AvgOfAvgOfFlowPressure,
qryJoin_GIS.AvgOfContractPressure, qryJoin_GIS.[Pressure Above Contract]
FROM qryJoin_GIS
WHERE (((qryJoin_GIS.BalanceSegment) Like [Enter Balance Segment (Use * as
Wildcard)]) AND ((qryJoin_GIS.[Pressure Above Contract])>30));


((qryJoin_GIS.[Pressure Above Contract])>30)) is the one that is not working
properly. It is returning all records, including those under 30. The data
type is number.

I'm new to this, so please excuse me if I don't use the correct terminology.

Bob Barrows said:
Kayla7509 said:
It returns all records, rather than using the threshold

Jerry Whittle said:
What do you mean by "doesn't work"? Error message? Wrong records
returned? --
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I'm somewhat new to access, and I'm trying to set up a parameter
query. I have tried Criteria: > [Enter Number], but that
doesn't work. Can anyone help me out?
Click to expand...
Click to expand...

Instead of making everyone guess, I suggest you show us the sql
statement (switch your query to SQL View using the View menu, or the
right-click context menu, or the toolbar button to see the sql
statement), tell us the datatype of the field you are trying to filter,
show us a few values from the field, and tell us what value you are
entering when you are prompted.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
This should work, so I have to assume that qryJoin_GIS is a saved query
and that the datatype of that Pressure field is being changed as a
result of something that the saved query is doing. Could you show us the
sql of the saved query?
SELECT qryJoin_GIS.MeterID, qryJoin_GIS.MeterName,
qryJoin_GIS.BalanceSegment, qryJoin_GIS.AvgOfAvgOfFlowPressure,
qryJoin_GIS.AvgOfContractPressure, qryJoin_GIS.[Pressure Above
Contract] FROM qryJoin_GIS
WHERE (((qryJoin_GIS.BalanceSegment) Like [Enter Balance Segment (Use
* as Wildcard)]) AND ((qryJoin_GIS.[Pressure Above Contract])>30));


((qryJoin_GIS.[Pressure Above Contract])>30)) is the one that is not
working properly. It is returning all records, including those under
30. The data type is number.

I'm new to this, so please excuse me if I don't use the correct
terminology.

Bob Barrows said:
Kayla7509 said:
It returns all records, rather than using the threshold

:

What do you mean by "doesn't work"? Error message? Wrong records
returned? --
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I'm somewhat new to access, and I'm trying to set up a parameter
query. I have tried Criteria: > [Enter Number], but that
doesn't work. Can anyone help me out?
Click to expand...

Instead of making everyone guess, I suggest you show us the sql
statement (switch your query to SQL View using the View menu, or the
right-click context menu, or the toolbar button to see the sql
statement), tell us the datatype of the field you are trying to
filter, show us a few values from the field, and tell us what value
you are entering when you are prompted.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.
Click to expand...
Click to expand...
 
SQL for qryJoin_GIS:

SELECT qryJoin_Base.ObjectID_GIS, qryJoin_Base.MeterID,
qryJoin_Base.MeterName, qryJoin_Base.BalanceSegment,
Avg(qryJoin_Base.AvgOfFlowPressure) AS AvgOfAvgOfFlowPressure,
Avg(qryJoin_Base.ContractPressure) AS AvgOfContractPressure,
IIf(Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure)>=0,Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure),0)
AS [Pressure Above Contract]
FROM qryJoin_Base
GROUP BY qryJoin_Base.ObjectID_GIS, qryJoin_Base.MeterID,
qryJoin_Base.MeterName, qryJoin_Base.BalanceSegment;

SQL for qryJoin_Base:

SELECT tblData_GIS.ObjectID_GIS, tblMeters.MeterID, tblMeters.MeterName,
tblMeters.BalanceSegment, tblData_QQM.ProductionMonth,
Avg(tblData_QQM.FlowPressure) AS AvgOfFlowPressure,
tblContractPressures.ContractPressure, tblMeters_SegmentBalance.Input,
tblMeters_SegmentBalance.InputExclude, tblMeters.ExcludeMeter
FROM (tblMeters_SegmentBalance INNER JOIN (((tblData_GIS INNER JOIN
tblData_QQM ON tblData_GIS.MeterID_GIS = tblData_QQM.MeterID) INNER JOIN
tblData_SCADA ON (tblData_GIS.BalanceSegment = tblData_SCADA.BalanceSegment)
AND (tblData_QQM.ProductionMonth = tblData_SCADA.ProductionMonth)) INNER JOIN
tblContractPressures ON (tblData_GIS.BalanceSegment =
tblContractPressures.BalanceSegment) AND (tblData_QQM.ProductionMonth =
tblContractPressures.ProductionMonth) AND (tblData_GIS.BalanceSegment =
tblContractPressures.BalanceSegment) AND (tblData_SCADA.BalanceSegment =
tblContractPressures.BalanceSegment) AND (tblData_SCADA.BalanceSegment =
tblContractPressures.BalanceSegment)) ON
(tblMeters_SegmentBalance.BalanceSegment =
tblContractPressures.BalanceSegment) AND (tblMeters_SegmentBalance.MeterID =
tblData_QQM.MeterID)) INNER JOIN tblMeters ON (tblMeters.MeterID =
tblData_QQM.MeterID) AND (tblData_SCADA.BalanceSegment =
tblMeters.BalanceSegment) AND (tblMeters_SegmentBalance.MeterID =
tblMeters.MeterID) AND (tblMeters_SegmentBalance.BalanceSegment =
tblMeters.BalanceSegment) AND (tblContractPressures.BalanceSegment =
tblMeters.BalanceSegment)
GROUP BY tblData_GIS.ObjectID_GIS, tblMeters.MeterID, tblMeters.MeterName,
tblMeters.BalanceSegment, tblData_QQM.ProductionMonth,
tblContractPressures.ContractPressure, tblMeters_SegmentBalance.Input,
tblMeters_SegmentBalance.InputExclude, tblMeters.ExcludeMeter
HAVING (((tblData_QQM.ProductionMonth)>=#7/1/2008# And
(tblData_QQM.ProductionMonth)<=#7/31/2008#) AND
((tblMeters_SegmentBalance.Input)=Yes) AND
((tblMeters_SegmentBalance.InputExclude)=No) AND
((tblMeters.ExcludeMeter)=No));

Thanks so much for the help!


Bob Barrows said:
This should work, so I have to assume that qryJoin_GIS is a saved query
and that the datatype of that Pressure field is being changed as a
result of something that the saved query is doing. Could you show us the
sql of the saved query?
SELECT qryJoin_GIS.MeterID, qryJoin_GIS.MeterName,
qryJoin_GIS.BalanceSegment, qryJoin_GIS.AvgOfAvgOfFlowPressure,
qryJoin_GIS.AvgOfContractPressure, qryJoin_GIS.[Pressure Above
Contract] FROM qryJoin_GIS
WHERE (((qryJoin_GIS.BalanceSegment) Like [Enter Balance Segment (Use
* as Wildcard)]) AND ((qryJoin_GIS.[Pressure Above Contract])>30));


((qryJoin_GIS.[Pressure Above Contract])>30)) is the one that is not
working properly. It is returning all records, including those under
30. The data type is number.

I'm new to this, so please excuse me if I don't use the correct
terminology.

Bob Barrows said:
Kayla7509 wrote:
It returns all records, rather than using the threshold

:

What do you mean by "doesn't work"? Error message? Wrong records
returned? --
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I'm somewhat new to access, and I'm trying to set up a parameter
query. I have tried Criteria: > [Enter Number], but that
doesn't work. Can anyone help me out?

Instead of making everyone guess, I suggest you show us the sql
statement (switch your query to SQL View using the View menu, or the
right-click context menu, or the toolbar button to see the sql
statement), tell us the datatype of the field you are trying to
filter, show us a few values from the field, and tell us what value
you are entering when you are prompted.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.
Click to expand...
Click to expand...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
Hmmm - the Avg function should be returning a number, but the IIF may be
doing something unexpected .... let's make sure the result of this is
numeric by changing that calculation to:

CDbl(
IIf(Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure)>=0
,Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure),0)
)
AS [Pressure Above Contract]
SQL for qryJoin_GIS:

SELECT qryJoin_Base.ObjectID_GIS, qryJoin_Base.MeterID,
qryJoin_Base.MeterName, qryJoin_Base.BalanceSegment,
Avg(qryJoin_Base.AvgOfFlowPressure) AS AvgOfAvgOfFlowPressure,
Avg(qryJoin_Base.ContractPressure) AS AvgOfContractPressure,
IIf(Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure)>=0
,Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure),0)
AS [Pressure Above Contract]
FROM qryJoin_Base
GROUP BY qryJoin_Base.ObjectID_GIS, qryJoin_Base.MeterID,
qryJoin_Base.MeterName, qryJoin_Base.BalanceSegment;

SQL for qryJoin_Base:

SELECT tblData_GIS.ObjectID_GIS, tblMeters.MeterID,
tblMeters.MeterName, tblMeters.BalanceSegment,
tblData_QQM.ProductionMonth, Avg(tblData_QQM.FlowPressure) AS
AvgOfFlowPressure, tblContractPressures.ContractPressure,
tblMeters_SegmentBalance.Input,
tblMeters_SegmentBalance.InputExclude, tblMeters.ExcludeMeter
FROM (tblMeters_SegmentBalance INNER JOIN (((tblData_GIS INNER JOIN
tblData_QQM ON tblData_GIS.MeterID_GIS = tblData_QQM.MeterID) INNER
JOIN tblData_SCADA ON (tblData_GIS.BalanceSegment =
tblData_SCADA.BalanceSegment) AND (tblData_QQM.ProductionMonth =
tblData_SCADA.ProductionMonth)) INNER JOIN tblContractPressures ON
(tblData_GIS.BalanceSegment = tblContractPressures.BalanceSegment)
AND (tblData_QQM.ProductionMonth =
tblContractPressures.ProductionMonth) AND (tblData_GIS.BalanceSegment
= tblContractPressures.BalanceSegment) AND
(tblData_SCADA.BalanceSegment = tblContractPressures.BalanceSegment)
AND (tblData_SCADA.BalanceSegment =
tblContractPressures.BalanceSegment)) ON
(tblMeters_SegmentBalance.BalanceSegment =
tblContractPressures.BalanceSegment) AND
(tblMeters_SegmentBalance.MeterID = tblData_QQM.MeterID)) INNER JOIN
tblMeters ON (tblMeters.MeterID = tblData_QQM.MeterID) AND
(tblData_SCADA.BalanceSegment = tblMeters.BalanceSegment) AND
(tblMeters_SegmentBalance.MeterID = tblMeters.MeterID) AND
(tblMeters_SegmentBalance.BalanceSegment = tblMeters.BalanceSegment)
AND (tblContractPressures.BalanceSegment = tblMeters.BalanceSegment)
GROUP BY tblData_GIS.ObjectID_GIS, tblMeters.MeterID,
tblMeters.MeterName, tblMeters.BalanceSegment,
tblData_QQM.ProductionMonth, tblContractPressures.ContractPressure,
tblMeters_SegmentBalance.Input,
tblMeters_SegmentBalance.InputExclude, tblMeters.ExcludeMeter
HAVING (((tblData_QQM.ProductionMonth)>=#7/1/2008# And
(tblData_QQM.ProductionMonth)<=#7/31/2008#) AND
((tblMeters_SegmentBalance.Input)=Yes) AND
((tblMeters_SegmentBalance.InputExclude)=No) AND
((tblMeters.ExcludeMeter)=No));

Thanks so much for the help!


Bob Barrows said:
This should work, so I have to assume that qryJoin_GIS is a saved
query and that the datatype of that Pressure field is being changed
as a result of something that the saved query is doing. Could you
show us the sql of the saved query?
SELECT qryJoin_GIS.MeterID, qryJoin_GIS.MeterName,
qryJoin_GIS.BalanceSegment, qryJoin_GIS.AvgOfAvgOfFlowPressure,
qryJoin_GIS.AvgOfContractPressure, qryJoin_GIS.[Pressure Above
Contract] FROM qryJoin_GIS
WHERE (((qryJoin_GIS.BalanceSegment) Like [Enter Balance Segment
(Use * as Wildcard)]) AND ((qryJoin_GIS.[Pressure Above
Contract])>30));


((qryJoin_GIS.[Pressure Above Contract])>30)) is the one that is not
working properly. It is returning all records, including those
under
30. The data type is number.

I'm new to this, so please excuse me if I don't use the correct
terminology.
Click to expand...
Click to expand...
Click to expand...
 
That did it. Thank you!!

Bob Barrows said:
Hmmm - the Avg function should be returning a number, but the IIF may be
doing something unexpected .... let's make sure the result of this is
numeric by changing that calculation to:

CDbl(
IIf(Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure)>=0
,Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure),0)
)
AS [Pressure Above Contract]
SQL for qryJoin_GIS:

SELECT qryJoin_Base.ObjectID_GIS, qryJoin_Base.MeterID,
qryJoin_Base.MeterName, qryJoin_Base.BalanceSegment,
Avg(qryJoin_Base.AvgOfFlowPressure) AS AvgOfAvgOfFlowPressure,
Avg(qryJoin_Base.ContractPressure) AS AvgOfContractPressure,
IIf(Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure)>=0
,Avg(qryJoin_Base!AvgOfFlowPressure-qryJoin_Base!ContractPressure),0)
AS [Pressure Above Contract]
FROM qryJoin_Base
GROUP BY qryJoin_Base.ObjectID_GIS, qryJoin_Base.MeterID,
qryJoin_Base.MeterName, qryJoin_Base.BalanceSegment;

SQL for qryJoin_Base:

SELECT tblData_GIS.ObjectID_GIS, tblMeters.MeterID,
tblMeters.MeterName, tblMeters.BalanceSegment,
tblData_QQM.ProductionMonth, Avg(tblData_QQM.FlowPressure) AS
AvgOfFlowPressure, tblContractPressures.ContractPressure,
tblMeters_SegmentBalance.Input,
tblMeters_SegmentBalance.InputExclude, tblMeters.ExcludeMeter
FROM (tblMeters_SegmentBalance INNER JOIN (((tblData_GIS INNER JOIN
tblData_QQM ON tblData_GIS.MeterID_GIS = tblData_QQM.MeterID) INNER
JOIN tblData_SCADA ON (tblData_GIS.BalanceSegment =
tblData_SCADA.BalanceSegment) AND (tblData_QQM.ProductionMonth =
tblData_SCADA.ProductionMonth)) INNER JOIN tblContractPressures ON
(tblData_GIS.BalanceSegment = tblContractPressures.BalanceSegment)
AND (tblData_QQM.ProductionMonth =
tblContractPressures.ProductionMonth) AND (tblData_GIS.BalanceSegment
= tblContractPressures.BalanceSegment) AND
(tblData_SCADA.BalanceSegment = tblContractPressures.BalanceSegment)
AND (tblData_SCADA.BalanceSegment =
tblContractPressures.BalanceSegment)) ON
(tblMeters_SegmentBalance.BalanceSegment =
tblContractPressures.BalanceSegment) AND
(tblMeters_SegmentBalance.MeterID = tblData_QQM.MeterID)) INNER JOIN
tblMeters ON (tblMeters.MeterID = tblData_QQM.MeterID) AND
(tblData_SCADA.BalanceSegment = tblMeters.BalanceSegment) AND
(tblMeters_SegmentBalance.MeterID = tblMeters.MeterID) AND
(tblMeters_SegmentBalance.BalanceSegment = tblMeters.BalanceSegment)
AND (tblContractPressures.BalanceSegment = tblMeters.BalanceSegment)
GROUP BY tblData_GIS.ObjectID_GIS, tblMeters.MeterID,
tblMeters.MeterName, tblMeters.BalanceSegment,
tblData_QQM.ProductionMonth, tblContractPressures.ContractPressure,
tblMeters_SegmentBalance.Input,
tblMeters_SegmentBalance.InputExclude, tblMeters.ExcludeMeter
HAVING (((tblData_QQM.ProductionMonth)>=#7/1/2008# And
(tblData_QQM.ProductionMonth)<=#7/31/2008#) AND
((tblMeters_SegmentBalance.Input)=Yes) AND
((tblMeters_SegmentBalance.InputExclude)=No) AND
((tblMeters.ExcludeMeter)=No));

Thanks so much for the help!


Bob Barrows said:
This should work, so I have to assume that qryJoin_GIS is a saved
query and that the datatype of that Pressure field is being changed
as a result of something that the saved query is doing. Could you
show us the sql of the saved query?

Kayla7509 wrote:
SELECT qryJoin_GIS.MeterID, qryJoin_GIS.MeterName,
qryJoin_GIS.BalanceSegment, qryJoin_GIS.AvgOfAvgOfFlowPressure,
qryJoin_GIS.AvgOfContractPressure, qryJoin_GIS.[Pressure Above
Contract] FROM qryJoin_GIS
WHERE (((qryJoin_GIS.BalanceSegment) Like [Enter Balance Segment
(Use * as Wildcard)]) AND ((qryJoin_GIS.[Pressure Above
Contract])>30));


((qryJoin_GIS.[Pressure Above Contract])>30)) is the one that is not
working properly. It is returning all records, including those
under
30. The data type is number.

I'm new to this, so please excuse me if I don't use the correct
terminology.
Click to expand...
Click to expand...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
Back
Top