Ran the macro and got the following error: BASIC runtime error.
Property or method not found: Drawpage
You must run the macro FROM THE FORM.
Open “frm_Program History” (nor Design mode, open it) and there
Tools > Macros > Run Macro
Success. Received the message “teste”.
Now you can vincule the routine to the button as I explained before.
And begin composing the filter.
...
'set variable for the subform:
oSF = oFM.getByName("ProgHistSub")
'set the variables for the 2 date boxes:
oDFS = oFM.getByName("StartDate")
oDFE = oFM.getByName("EndDate")
'get the values inserted in both controls:
ds = oDFS.Date
de = oDFE.Date
Run code via the button (to check it). Result will be same “teste!”.
Just a step-by-step to assure all the names are correct.
IT’S NECESSARY TO HAVE DATES SET IN BOTH DATE BOXES, otherwise will raise an error. Later after all working we treat errors of course.
I have to apologize. I can’t find a reference as to how to link the code you provided to the button. I am very new to Libreoffice Base and am learning as I go. Does this code go into the SQL area on the button properties? If not, where?
Edit Form
select the button > (right click) Control Properties > tab Events >
Approve action (the first, it’s the OnClick of Access) > Macro > you select the only macro existing > Save (of course).
But no need to do this yet. More important to check the routine for the names and after this compose the filter.
To check the objects names is the last step for composing the filter string.
I was unsure as to where the last bit of code you sent was to be placed. So, I place it in the Macro just before the “Msge “teste””. When I ran it from the form I got the message “teste” as a result. Also, when I clicked the button it gave me the message “teste”.
I hope I placed the code in the correct place.
So it’s all ok and time to compose the filter.
Pay attention to the double and single quotes.
filtro = """Date"" >= '" & CDateFromUnoDate(ds) & "' AND "
filtro = filtro & """Date"" <= '" & CDateFromUnoDate(de) & "'"
MsgBox filtro
I’ll send an image of the form the string must have at the MsgBox.
Note that “Date” is the column’s name (= filter field) of your table datasource of the subform, if I rebember it well.
CDateFromUnodate will return a string in your locale format, able to be inserted in the string.
With this code added to the Macro, the message box returns the start date and the end date.
Ok, but there was a lapse of mine!
The date format for the database must be in the American. Today I’m in a machine with brazilian locale so I needed to insert manually ‘mm/dd/yy’ in the string.
I completed here (yesterday I did on another machine with en-US locale).
So let’s say you selected at date boxes
start = May 01, 2025
end = May 31, 2025
the string must look like
It must be in American date format.
Of course “dia” is the name of my column here.
Ok, I completed it here.
So if your locale is not American you can’t use the CDateFromUnoDate and must build manually the American date format as:
filtro = """dia"" >= '" & ds.month & "/" & ds.day & "/" & ds.year & "' AND "
filtro = filtro & """dia"" <= '" & de.month & "/" & de.day & "/" & de.year & "'"
oSF.Filter = filtro
oSF.reload
Form filtered for dates between June 1 and today.
This last entry for today I inserted before in the form to check it is editable.
I am in the USA. I added the following code to the Macro:
oSF.Filter = filtro
oSF.reload
Here is what comes up when I click the OK button:
When I click the ‘more’ button I get this:
If you run this exact query in the SQL editor results show up?
No. I opened the form- “frm-ProgramHistory” and pasted the Macro code in the SQL editor and ran the code. I received the following errors:
clicked the More button:
closed that message and clicked the OK button from the first error message and got:
Clicked More again and got:
If it helps, this is the entire Macro code:
Option Explicit
Sub FiltroPeriodos
'declare variables for Main (oFM), SubForm (oSF) and date controls start and end period;
Dim oFM As Object, oSF As Object, oDFS As Object, oDFE As Object
'declare variables for the date fields contents (the dates inserted there):
Dim ds As New com.sun.star.util.Date, de As New com.sun.star.util.Date
'declare variable for the string of the filter:
Dim filtro As String
'get a reference to the main form:
oFM = ThisComponent.Drawpage.Forms.getByName(“frm_ProgramHistory”)
'set variable for the subform:
oSF = oFM.getByName(“ProgHistSub”)
'set the variables for the 2 date boxes:
oDFS = oFM.getByName(“StartDate”)
oDFE = oFM.getByName(“EndDate”)
'get the values inserted in both controls:
ds = oDFS.Date
de = oDFE.Date
filtro = “”“Date”" >= ‘" & CDateFromUnoDate(ds) & "’ AND "
filtro = filtro & “”“Date”" <= ‘" & CDateFromUnoDate(de) & "’"
’ MsgBox filtro
filtro = “”“Date”" >= ‘" & ds.month & “/” & ds.day & “/” & ds.year & "’ AND "
filtro = filtro & “”“Date”" <= ‘" & de.month & “/” & de.day & “/” & de.year & "’"
oSF.Filter=filtro
oSF.reload
End Sub
No, I mean run the query in the SQL editor to check the date Format.
Queries > run query in SQL mode:
SELECT * FROM “Historical Data” WHERE
“Date” >= ‘01/01/1975’ AND “Date” <= ‘12/31/1975’
Run to see if it returns the results.
Is it HSQLDB?
Here I’m using Firebird.
As you see apparently the filter string was formed ok.
Don’t know if this (the database) can be the diference (?)
I’sorry: don’t know why.
I think the problem is here as if the query does not work…
You could try using the ISO Format
‘1975-01-01’
‘1975-12-31’
But don’t know, SORRY!
Just asking, do you know the built-in filter methods?
Let’s remember the question:
By the way: learned how to compose a filter for another form from yourself @Villeroy!
Question is: here it’s working. Don’t know why this Java Exception and neither the query runs (???)