[Solved] Criteria filter in result

Creating tables and queries
Post Reply
christos73
Posts: 16
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

[Solved] Criteria filter in result

Post 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.
Last edited by christos73 on Tue Dec 25, 2018 11:34 pm, edited 2 times in total.
Apache OpenOffice 4.1.7 - -
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: criteria filter in result

Post 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.
christos73
Posts: 16
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

Re: criteria filter in result

Post 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 3227 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!
Last edited by christos73 on Wed Dec 26, 2018 1:32 am, edited 4 times in total.
Apache OpenOffice 4.1.7 - -
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: criteria filter in result

Post 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.
christos73
Posts: 16
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

Re: Criteria filter in result

Post 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!
Apache OpenOffice 4.1.7 - -
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Criteria filter in result

Post 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.
christos73
Posts: 16
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

Re: Criteria filter in result

Post 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!
Last edited by christos73 on Tue Dec 25, 2018 11:37 pm, edited 1 time in total.
Apache OpenOffice 4.1.7 - -
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Criteria filter in result

Post 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` 
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
christos73
Posts: 16
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

Re:[Solved] Criteria filter in result

Post by christos73 »

:super:
Euxaristo
Means Thank you!


That was it! UnklDonald418 !

bye bye take care!
Apache OpenOffice 4.1.7 - -
Post Reply