I am re writing an Access database with Libreoffice 7 Base. It has a number of Tables, Queries Forms and Reports. The master table was created in Excel via an export from Access. and imported to Calc. Base gets its information from that. I will have to hand over this to another in a year and do not want to have both Access and LO Base necessary
I have a query in Access with the criteria set to look at two values in a table. One is straight forward, the other is calculated. Example, the SQL will contain…
Where “STATUS” = ‘ACTIVE’ AND "LASTDUES = (Year(Now()))-“1”
Both are “short text” types as LASTDUES can be a number or plain text.
(Year(Now())) returns the current year then subtracts “1”. For 2021 the result would be 2020 and it will produce all records fitting the query.
I have not come up with a way to do this in Base (V6 or 7). If I just use (Year(Now()))-“1”, it runs without error but only blank records are produced. It looks like the format of the field needs to be in integer format.
Any help for an old man would be appriciated.