[Solved] Criteria filter in result

Creating tables and queries

[Solved] Criteria filter in result

Postby christos73 » Fri Dec 21, 2018 1:18 am

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.6 - Win 7 Home - Bible - New Testament
christos73
 
Posts: 13
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

Re: criteria filter in result

Postby Sliderule » Fri Dec 21, 2018 5:02 am

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.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: criteria filter in result

Postby christos73 » Fri Dec 21, 2018 3:20 pm

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 628 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.6 - Win 7 Home - Bible - New Testament
christos73
 
Posts: 13
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

Re: criteria filter in result

Postby Sliderule » Fri Dec 21, 2018 3:51 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: Criteria filter in result

Postby christos73 » Sat Dec 22, 2018 5:21 pm

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   Expand viewCollapse view
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.6 - Win 7 Home - Bible - New Testament
christos73
 
Posts: 13
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

Re: Criteria filter in result

Postby Sliderule » Sat Dec 22, 2018 5:42 pm

Code: Select all   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: Criteria filter in result

Postby christos73 » Sat Dec 22, 2018 9:29 pm

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.6 - Win 7 Home - Bible - New Testament
christos73
 
Posts: 13
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece

Re: Criteria filter in result

Postby UnklDonald418 » Sat Dec 22, 2018 11:58 pm

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   Expand viewCollapse view
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re:[Solved] Criteria filter in result

Postby christos73 » Sun Dec 23, 2018 2:08 am

:super:
Euxaristo
Means Thank you!


That was it! UnklDonald418 !

bye bye take care!
Apache OpenOffice 4.1.6 - Win 7 Home - Bible - New Testament
christos73
 
Posts: 13
Joined: Sat Jan 20, 2018 4:26 pm
Location: Greece


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 5 guests