Page 1 of 1

[Solved] Criteria filter in result

Posted: Fri Dec 21, 2018 1:18 am
by christos73
Hello everyone,

I'm trying to find a query or a filter criteria to display all records with "0.01" or "-0.01" from "Items" field.
queryaskb.png
Any advice ?

I 've tyred >=0.01 etc. or LIKE etc... but i can't find it.

Regards Christos.

Re: criteria filter in result

Posted: Fri Dec 21, 2018 5:02 am
by Sliderule
christos73 wrote:
I'm trying to find a query or a filter criteria to display all records with "0.01" or "-0.01" from "Items" field.
I am not certain which you want, so, please try both and choose ( and let us know ) what worked for youl
  1. If you want to return ONLY values of "Items" when it is equal to either:
    1. -0.01
    2. 0.01
    As in your third graphic, on the "Items" column
    1. In the Criterion line, enter: -0.01
    2. and, on the or line, enter: 0.01
  2. If you want to return all records where "Items" is between the values of -0.01 and 0.01, enter:
    1. In the Criterion line, enter: Between -0.01 and 0.01
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: criteria filter in result

Posted: Fri Dec 21, 2018 3:20 pm
by christos73
Hi Sliderule !

Thanks for the help,

> In the Criterion line, enter: -0.01
> and, on the or line, enter: 0.01

this the solution!

But in the database which I'm trying to use it
in one column where i use
Field : `result` + `netprice`
Alias : Result
when i enter criterion it gives me the follow message.
queryaskb2.png
queryaskb2.png (9.12 KiB) Viewed 3225 times
In the other column where is use the calculation in
Field:`tabl1`.`netprice1` * 0.13 + `orderprice1` - `netprice1`
Alias: Result2,

works fine but it returns me 3 records ONLY with value 0.01 and 3 with -0.01 (total) 6, from about 100-120 records of 0.01 and -0.01
i don't get it...
c ya!

Re: criteria filter in result

Posted: Fri Dec 21, 2018 3:51 pm
by Sliderule
That is because you have not manipulated the Criterion line correctly.

Your original question was answered. Your new issue is just that, a new issue, and, should be a new forum post.

You never told us the database back-end you are using . . . makes a difference.

Show the SQL for the Query ( Select statement ) . . . NOT a graphic.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: Criteria filter in result

Posted: Sat Dec 22, 2018 5:21 pm
by christos73
Ok,

I use an application where it stores data in a .mdb file and i add records through oopenoffice form ODBC connection etc.
The table it uses "Data type" of ms Access as "Short Text" while oo it shows me as Text[VARCHAR] in table.
And the query is :

Code: Select all

SELECT `netprice`, `orderprice`, `ordertot`, `serial`, `table1`.` netprice ` * 0.24 AS `result`, `result` + ` netprice ` AS `TOT RESULT`, ` ordertot ` - `TOT RESULT` AS `DIFFERENCE`, ` table1 `.` netprice ` * 0.24 + ` netprice ` - ` netprice ` AS `DIFFERENCE2`, `orderdate` FROM `table1` End Sub
Now what i want is to see how many records the have difference from zero.
And display only them in screen without the zeros.
With this query it displays all of them.

bye!

Re: Criteria filter in result

Posted: Sat Dec 22, 2018 5:42 pm
by Sliderule

Code: Select all

SELECT
   `netprice`,
   `orderprice`,
   `ordertot`,
   `serial`,
   `table1`.`netprice` * 0.24 AS `result`,
   (`table1`.`netprice` * 0.24) + `netprice` AS `TOT RESULT`,
   `ordertot` - ((`table1`.`netprice` * 0.24) + `netprice`) AS `DIFFERENCE`,
   `table1`.`netprice` * 0.24 + `netprice` - `netprice` AS `DIFFERENCE2`,
   `orderdate`
FROM
   `table1`
WHERE `Items` = -0.01 
   OR `Items` = 0.01
Note: the above calculation for `DIFFERENCE2` makes no sense. That is, you are adding `netprice` and then subtracting the same value ( + `netprice` - `netprice` ). Furthermore, I have no idea why you included END SUB in the SQL statement.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: Criteria filter in result

Posted: Sat Dec 22, 2018 9:29 pm
by christos73
End sub is typing mistake.

Criterion 0.01 and -0.01 works fine but I cannot understand why it returns me only 6 from the 100 about records.
6 negative and 6 positive

When I'll have some time I will try again....

Thanks Sliderule!

Re: Criteria filter in result

Posted: Sat Dec 22, 2018 11:58 pm
by UnklDonald418
Assuming `table1`.` netprice ` has 2 decimal places, when you do a calculation like `table1`.` netprice ` * 0.24 the rules of mathematics tells us that the result will have 4 decimal places. Try

Code: Select all

ROUND(`table1`.` netprice ` * 0.24, 2)  AS `result` 

Re:[Solved] Criteria filter in result

Posted: Sun Dec 23, 2018 2:08 am
by christos73
:super:
Euxaristo
Means Thank you!


That was it! UnklDonald418 !

bye bye take care!