Convert an Access query criteria to base

[eesamp709]

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.
eesamp

[RobertG]

Shouldn’t it be

Where "STATUS" = 'ACTIVE' AND "LASTDUES" = Year(Now()) - 1

Note: YEAR() will only work with internal HSQLDB, not with Firebird. The following code should run with both:

Where "STATUS" = 'ACTIVE' AND "LASTDUES" = EXTRACT (Year FROM CURRENT_DATE) - 1

[Villeroy]

First and foremost we have to know which database we are talking about. No, there is no such thing as a “Base database”. The status bar or your database window indicates the type of connection on the left side.

[Ratslinger]

Hello,

With HSQLDB embedded database:

[eesamp709]

My base app is HSQLDB.
My access application uses a form to edit and add records in a table. The main form is write protected but a secong “editing” form is used to add a record or edit the data in an existing record. Other forms are used to generate reports, mailing lists etc. I have not figured out to recreate these functions in LO Base. I am looking at an Excel version then port it to Calc. Will see how this goes.

[Ratslinger]

@eesamp709

I have not figured out to recreate these functions in LO Base.

Not quite certain if you are posing a question. Sounds as if you may have many questions other than the original post. If so please present each individually.

[eesamp709]

I still have questions but have t shelve the project for now. When I do get back to it, I’ll bring them up. Thanks for the help so far