How to filter between two dates in Base

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
zenira
Posts: 88
Joined: Sun May 11, 2014 11:24 am

How to filter between two dates in Base

Post by zenira »

Hi friends
I got a problem about to filter between two dates in a base table. my sql code is to filter only january month here:

Code: Select all

dim firstdate,lastdate as object
firstdate=CDate("01/01/2018")
lastdate=CDate("01/31/2018")
ResultSet = Statement.executeQuery("SELECT * FROM MyDataBase WHERE DATE BETWEEN '"+firstdate+"' AND '"+lastdate+"'    " )
:crazy: :knock: what is wrong. please help me
LibreOffice 4.1.0.4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to filter between two dates in Base

Post by Villeroy »

"01/31/2018" is a text.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
zenira
Posts: 88
Joined: Sun May 11, 2014 11:24 am

Re: How to filter between two dates in Base

Post by zenira »

I convert it from string to date with CDate function. wrong?
LibreOffice 4.1.0.4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to filter between two dates in Base

Post by Villeroy »

1) CDate("01/31/2018") returns a Basic date only if the application runs with a US English locale. It fails if you switch the locale setting to anything else. Apart from that, a Basic date is completely useless outside the Basic context.
2) You try to concatenate an SQL statement without debugging your code.

Code: Select all

sql = "SELECT * FROM MyDataBase WHERE DATE BETWEEN '"+firstdate+"' AND '"+lastdate+"'    "
print sql
Is this the string that works in query design? Can you run a working query in the query designer?

3) What you are trying to do can be acomplished easily without macro.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
zenira
Posts: 88
Joined: Sun May 11, 2014 11:24 am

Re: How to filter between two dates in Base

Post by zenira »

I have a base database file and I want to get datas from base table to calc sheet. So code will work in calc
LibreOffice 4.1.0.4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to filter between two dates in Base

Post by Villeroy »

Store this query in your Base file:

Code: Select all

SELECT * FROM "MyTable" WHERE "DateColumn" BETWEEN :D1 AND :D2
Open your Calc document.
menu:View>Data Sources (F4)
Navigate to your database and the query
Drag the query icon into the sheet.
You will be prompted for 2 dates.
Save the spreadhsheet
-------------------------
Every time you select a cell in the import range and call menu:Data>Refresh you will be prompted for 2 dates and get the current data set imported in your sheet.

A slightly more advanced example on how to link a spreadhsheet and database: viewtopic.php?t=88516&p=416210#p416210
It includes one line of macro code to refresh the report sheet when you click a form button.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply