parameter query using "greater than..."

[Guest]

Is there a way to write a parameter query using "greater than" rather than
"between... And..."? I tried using " >[greater than:] ". It produced a
query, but with erroneous data.

 

[Dodo]

Is there a way to write a parameter query using "greater than" rather
than "between... And..."? I tried using " >[greater than:] ". It
produced a query, but with erroneous data.

Just use >value as a parameter

 

[Guest]

Thanks. That does work, but I was hoping I could re-run the report using
various scenarios without having to go back to the query structure every time.
--
Patti

Is there a way to write a parameter query using "greater than" rather
than "between... And..."? I tried using " >[greater than:] ". It
produced a query, but with erroneous data.

Just use >value as a parameter

 

[Dodo]

Thanks. That does work, but I was hoping I could re-run the report
using various scenarios without having to go back to the query
structure every time.

Change it to: >[Value larger than?]

 

[Lynn Trapp]

Patti,
You should be able to use something like this for a parameter:

[Enter Some Value]

If you want to get a records between some range you could try either:

[Enter Some Value] AND < [Enter Another Value]

= [Enter Some Value] AND < [Enter Another Value]

[Enter Some Value] AND <= [Enter Another Value]
OR

= [Enter Some Value] AND <= [Enter Another Value]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Thanks. That does work, but I was hoping I could re-run the report using
various scenarios without having to go back to the query structure every
time.
--
Patti

Is there a way to write a parameter query using "greater than" rather
than "between... And..."? I tried using " >[greater than:] ". It
produced a query, but with erroneous data.

Just use >value as a parameter

 

[Guest]

I tried that, but it won't query the proper data. I'm trying to query off an
expression field that calculates a percentage. Might that have anything to
do with it? Heres the sql:

SELECT tblData.Date, tblData.HourEnding, tblData.Telemetered,
tblData.Metered, IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered]))
AS PercentDiff
FROM tblData
GROUP BY tblData.Date, tblData.HourEnding, tblData.Telemetered,
tblData.Metered
HAVING (((tblData.Date) Between [Start Date:] And [End Date:]) AND
((IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])))>[Value larger
than?]))
ORDER BY IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])) DESC;


Patti

Thanks. That does work, but I was hoping I could re-run the report
using various scenarios without having to go back to the query
structure every time.

Change it to: >[Value larger than?]

 

[Guest]

Thanks, Lynn. For some reason, that won't work. If I use the parameter

[enter a greater than value] and enter .01 in the dialog box, (for 1%) it

gives me erroneous data. However, if I just enter > .01 in the criteria
portion of the query design, I get correct data. It seems like it doesn't
want to accept variable data. I posted the sql in a previous post, but here
it is again:

SELECT tblData.Date, tblData.HourEnding, tblData.Telemetered,
tblData.Metered, IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered]))
AS PercentDiff
FROM tblData
WHERE (((tblData.Date) Between [Start Date:] And [End Date:]) AND
((IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])))>[Enter greater
than value]))
ORDER BY IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])) DESC;

--
Patti

Patti,
You should be able to use something like this for a parameter:

[Enter Some Value]

If you want to get a records between some range you could try either:

[Enter Some Value] AND < [Enter Another Value]

= [Enter Some Value] AND < [Enter Another Value]

[Enter Some Value] AND <= [Enter Another Value]
OR

= [Enter Some Value] AND <= [Enter Another Value]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Thanks. That does work, but I was hoping I could re-run the report using
various scenarios without having to go back to the query structure every
time.
--
Patti

Is there a way to write a parameter query using "greater than" rather
than "between... And..."? I tried using " >[greater than:] ". It
produced a query, but with erroneous data.


Just use >value as a parameter

 

[Dodo]

I tried that, but it won't query the proper data. I'm trying to query
off an expression field that calculates a percentage. Might that have
anything to do with it? Heres the sql:

SELECT tblData.Date, tblData.HourEnding, tblData.Telemetered,
tblData.Metered,
IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])) AS
PercentDiff FROM tblData
GROUP BY tblData.Date, tblData.HourEnding, tblData.Telemetered,
tblData.Metered
HAVING (((tblData.Date) Between [Start Date:] And [End Date:]) AND
((IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])))>[Value
larger than?]))
ORDER BY IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered]))
DESC;

Sorry, too difficult for me! ;-)))

It couldn't be that [Metered] can be null?

 

[John Spencer (MVP)]

Perhaps it will work better if you either declare your parameters or force their
type using type declarations.

PARAMETERS [Start Date:] DateTime, [End Date:] DateTime,
[Enter greater than value] IEEEDouble;
SELECT tblData.Date, tblData.HourEnding, tblData.Telemetered,
tblData.Metered,
IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])) AS PercentDiff
FROM tblData
WHERE tblData.Date Between [Start Date:] And [End Date:] AND
IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered]))>
CDbl([Enter greater than value])
ORDER BY IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])) DESC

Thanks, Lynn. For some reason, that won't work. If I use the parameter

[enter a greater than value] and enter .01 in the dialog box, (for 1%) it

gives me erroneous data. However, if I just enter > .01 in the criteria
portion of the query design, I get correct data. It seems like it doesn't
want to accept variable data. I posted the sql in a previous post, but here
it is again:

SELECT tblData.Date, tblData.HourEnding, tblData.Telemetered,
tblData.Metered, IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered]))
AS PercentDiff
FROM tblData
WHERE (((tblData.Date) Between [Start Date:] And [End Date:]) AND
((IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])))>[Enter greater
than value]))
ORDER BY IIf([Metered]=0,0,Abs(([Telemetered]-[Metered])/[Metered])) DESC;

--
Patti

Patti,
You should be able to use something like this for a parameter:

[Enter Some Value]

If you want to get a records between some range you could try either:

[Enter Some Value] AND < [Enter Another Value]

= [Enter Some Value] AND < [Enter Another Value]

[Enter Some Value] AND <= [Enter Another Value]
OR

= [Enter Some Value] AND <= [Enter Another Value]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Thanks. That does work, but I was hoping I could re-run the report using
various scenarios without having to go back to the query structure every
time.
--
Patti


:


Is there a way to write a parameter query using "greater than" rather
than "between... And..."? I tried using " >[greater than:] ". It
produced a query, but with erroneous data.


Just use >value as a parameter