How to filter between two dates in Base

Creating a macro - Writing a Script - Using the API

How to filter between two dates in Base

Postby zenira » Thu Feb 08, 2018 10:56 pm

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   Expand viewCollapse view
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
zenira
 
Posts: 88
Joined: Sun May 11, 2014 11:24 am

Re: How to filter between two dates in Base

Postby Villeroy » Thu Feb 08, 2018 11:14 pm

"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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26873
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to filter between two dates in Base

Postby zenira » Thu Feb 08, 2018 11:20 pm

I convert it from string to date with CDate function. wrong?
LibreOffice 4.1.0.4 on Windows 7
zenira
 
Posts: 88
Joined: Sun May 11, 2014 11:24 am

Re: How to filter between two dates in Base

Postby Villeroy » Thu Feb 08, 2018 11:54 pm

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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26873
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to filter between two dates in Base

Postby zenira » Fri Feb 09, 2018 7:42 am

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
zenira
 
Posts: 88
Joined: Sun May 11, 2014 11:24 am

Re: How to filter between two dates in Base

Postby Villeroy » Fri Feb 09, 2018 11:42 am

Store this query in your Base file:
Code: Select all   Expand viewCollapse view
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: https://forum.openoffice.org/en/forum/v ... 10#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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26873
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests