Page 1 of 1

How to filter between two dates in Base

Posted: Thu Feb 08, 2018 10:56 pm
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

Re: How to filter between two dates in Base

Posted: Thu Feb 08, 2018 11:14 pm
by Villeroy
"01/31/2018" is a text.

Re: How to filter between two dates in Base

Posted: Thu Feb 08, 2018 11:20 pm
by zenira
I convert it from string to date with CDate function. wrong?

Re: How to filter between two dates in Base

Posted: Thu Feb 08, 2018 11:54 pm
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.

Re: How to filter between two dates in Base

Posted: Fri Feb 09, 2018 7:42 am
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

Re: How to filter between two dates in Base

Posted: Fri Feb 09, 2018 11:42 am
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.