Greater than/Less than anomaly

[loasbyp]

This is a follow up to my other greater than/less than topic posted yesterday...

I've applied the NZ logic and all appears to be well. I now have a field being returned in my query that is

NZ([Tbl-Flight Model Output].[ROS %],0)

that brings back all records and when I apply the criteria

>=[forms]![Flt Rpt By Prod]![QryROSStart] AND <=[forms]![Flt Rpt By Prod]![QryROSEnd]

where QryROSStart is a zero value and QryROSEnd is one it continues to work fine (all records have between 0 and 100 held against them), however if I extend the range to -1 in QryROSStart the query doesn't return any rows!

Surely by extending the range (ie greater than or equal to minus one and less than or equal to plus one) shouldn't make a difference as all of the records are still within this range???

Any advice anyone can offer (on this topic!) would be much appreciated.

 

[giacomo]

I was having trouble trying to replicate this issue, can you post your full SQL and let us know what the data type is on the [ROS %] field?

Giacomo

 

[loasbyp]

Hi Giacomo and thanks for your reply.

The data type in the table is set as number with a percent format (zero decimal places) and I have not placed any formatting on the field in the query (although when I do format the field in the query as general number or percent, no results are returned when I run the query even if I try zero and one as the range which is the only range i can get to bring back the correct results without any formatting on the field in the query).

The SQL is: -

SELECT NZ([Tbl-Flight Model Output (By Prod) S07].[ROS %],0) AS [ROS %]
FROM [Tbl-Flight Model Output (By Prod) S07]
WHERE (((NZ([Tbl-Flight Model Output (By Prod) S07].[ROS %],0))>=[forms]![Flt Rpt By Prod S07]![QryROSStart] And (NZ([Tbl-Flight Model Output (By Prod) S07].[ROS %],0))<=[forms]![Flt Rpt By Prod S07]![QryROSEnd]));

I have also tried using the 'between' function as opposed to greater than and less than and this makes no difference.

P.S. Excellent advice in your normalisation links

 

[giacomo]

try this instead:

SELECT NZ([Tbl-Flight Model Output (By Prod) S07].[ROS %],0) AS [ROS %]
FROM [Tbl-Flight Model Output (By Prod) S07]
WHERE ((([Tbl-Flight Model Output (By Prod) S07].[ROS %])>=[forms]![Flt Rpt By Prod S07]![QryROSStart] And ([Tbl-Flight Model Output (By Prod) S07].[ROS %])<=[forms]![Flt Rpt By Prod S07]![QryROSEnd])) OR ((([Tbl-Flight Model Output (By Prod) S07].[ROS %]) Is Null));

hth,
Giacomo