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+"' " )
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.