Apache OpenOffice Wiki - User contributions [en]

Talk:Documentation/How Tos/Calc: Database functions

2009-01-07T14:59:52Z

Ztevepowell: /* Putting parameters in criteria */

My question is :

How to filter a database in Calc to include rows between two dates, where an earleir date than today is already defined in the spreadsheet?

TIA

Nicolas Beaudet

== Putting parameters in criteria ==

The way to have variable criteria is to put calculated expressions in the criteria range. For example:

{| class="wikitable"
|-
!
! A
! B
! C
|-
| 1
| ColA
|
|
|-
| 2
| = "<" & C2
|
| 42
|}

The criteria range is A1:A2, selecting the rows for which the value in column "ColA" is less than the value in C2 (42 in this case).

So if the database rows are:

{| class="wikitable"
|-
!
! X
! Y
! Z
|-
| 12
| ColA
| ColB
| ColC
|-
| 13
| 41
| 100
| 2
|-
| 14
| 43
| 1
| 200
|}

then the expression: =DSUM(X12:Z14; "ColC"; A1:A2) will calculate the value 2.

If the value of cell C2 (the parameter value for the criteria) is changed to 44, the result will be 202.

I'm not sure if the description proposed on this page shouldn't be enhanced to explain this.

--[[User:Ztevepowell|Zteve]] 15:59, 7 January 2009 (CET)

Talk:Documentation/How Tos/Calc: Database functions

2009-01-07T14:58:55Z

Ztevepowell: New section: Putting parameters in criteria