[Solved] Criteria filter in result
-
- Posts: 16
- Joined: Sat Jan 20, 2018 4:26 pm
- Location: Greece
[Solved] Criteria filter in result
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. Any advice ?
I 've tyred >=0.01 etc. or LIKE etc... but i can't find it.
Regards Christos.
I'm trying to find a query or a filter criteria to display all records with "0.01" or "-0.01" from "Items" field. 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 - -
Re: criteria filter in result
I am not certain which you want, so, please try both and choose ( and let us know ) what worked for youlchristos73 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.
- If you want to return ONLY values of "Items" when it is equal to either:
- -0.01
- 0.01
- In the Criterion line, enter: -0.01
- and, on the or line, enter: 0.01
- If you want to return all records where "Items" is between the values of -0.01 and 0.01, enter:
- In the Criterion line, enter: Between -0.01 and 0.01
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 16
- Joined: Sat Jan 20, 2018 4:26 pm
- Location: Greece
Re: criteria filter in result
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. 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!
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. 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 - -
Re: criteria filter in result
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.
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.
-
- Posts: 16
- Joined: Sat Jan 20, 2018 4:26 pm
- Location: Greece
Re: Criteria filter in result
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 :
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!
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
And display only them in screen without the zeros.
With this query it displays all of them.
bye!
Apache OpenOffice 4.1.7 - -
Re: Criteria filter in result
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
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 16
- Joined: Sat Jan 20, 2018 4:26 pm
- Location: Greece
Re: Criteria filter in result
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!
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 - -
-
- Volunteer
- Posts: 1547
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Criteria filter in result
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Posts: 16
- Joined: Sat Jan 20, 2018 4:26 pm
- Location: Greece
Re:[Solved] Criteria filter in result
Euxaristo
Means Thank you!
That was it! UnklDonald418 !
bye bye take care!
Apache OpenOffice 4.1.7 - -