Setting dates in base query

I had a database where I used this criterion to get only information between the indicated date. It looked like this: BETWEEN #01/01/2021# AND #12/31/2040# I screwed up that database, so I am trying to rebuild my queries. this function as typed will not work. I don’t know what I am doing wrong. Please help.

Thank you, I did figure out that I hadn’t set the field to dates, hence my criterion wasn’t working. However, now that I have it working, is there a limit as to how far out I can set dates? I have a few that have the set date of 2040, but when I run the query, those people don’t show up on the list. I can’t think of any other reason it wouldn’t pick up the ones set at 12/31/2040. I am running the latest version of the software.


Need to know the database used. On the main Base screen on the status bar (very bottom line) in the center it tells the database and on the left the connection. What are these?

Most likely it is something like:

BETWEEN '01/01/21' AND '12/31/40'

or possibly

BETWEEN '01/01/2021' AND '12/31/2040'

Aside from the actual question, you may want to review this → Ask/Getting Started


Still as a loss as to the database you are using. Seems to be a limit in LO as to the year allowed - 2039 was max I could get. There is a setting from menu Tools->Option then under LibreOffice->General in right panel is setting for two digit year. regardless if using two or four digit year, testing only allowed through 2039.

You should report this as a bug here → Bugzilla

Please note, you stated:

I am running the latest version of the software.

There is more than one ‘latest version’. Always specify the total version number. I am using LibreOffice v7.1.2.2 from TDF (The Document Foundation) on Ubuntu 20.04.2.