Ask Your Question

filter by date via macro

asked 2016-03-14 09:22:25 +0200

cmpd gravatar image

0 down vote favorite

I am trying to re-use the following search macro to also filter by date.

Sub SearchByDSS 
dim oFilter as object
dim oFormCtl as object 
oFormCtl = ThisComponent.Drawpage.Forms.getByName("Main")
oFilter = oFormCtl.getByName("Control")

if oFilter.CurrentValue <> "" then
oFormCtl.Filter = "table.Field LIKE" + > "'"+oFilter.CurrentValue+"'"
oFormCtl.ApplyFilter = True
oFormCtl.ApplyFilter = False
end if 
End Sub

I changed the code to something like:

oFormCtl.Filter = "table.Field >=" + {D '" & oFilter.Text & "'}"

but still no luck. Any ideas? Thanks in advance!

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2016-03-14 13:31:47 +0200

Ratslinger gravatar image

There is a problem with the structure of the filter - original and yours. Attached is an example routine. You will need to add your own code to complete it. Filter.odt It is has comment lines explaining what is happening.

edit flag offensive delete link more


This is not exactly what I am looking for. The date value should come from the date field. See my own answer above. But thanks anyway!

cmpd gravatar imagecmpd ( 2016-03-15 09:28:09 +0200 )edit

answered 2016-03-15 09:26:17 +0200

cmpd gravatar image

I tried the following (with acces2base):

Sub FilterbyDate
Dim dDate As Date, ofForm as object, sfilter as string
dDate = Forms("MAIN").Controls("FilterDate").Value
ddate = format(v,"yyyy-mm-dd")
ofForm = Forms("Main")

sfilter = "FastTel.Reminder >=" + "{d'" & dDate & "'}"
ofForm.Filter = sfilter
ofForm.Filteron = True
End Sub

But I get: SQL-Status: 42000

Syntax error for DATE escape sequence '00:00:00'

edit flag offensive delete link more


If FilterDate is a List Box, you are accessing the first entry in the list (blank line?) and not the selection. In this case use SelectedValue and not Value. Don't see where dDate is formatted for the filter - not understanding line with ddate = format(...

Ratslinger gravatar imageRatslinger ( 2016-03-15 15:35:34 +0200 )edit

Also go back to the filter shown in sample - get rid of {d } - I'm guessing FastTel.Remainder is a date you're comparing against.

Ratslinger gravatar imageRatslinger ( 2016-03-15 15:48:33 +0200 )edit

answered 2016-03-18 11:20:01 +0200

pierre-yves samyn gravatar image


The filter expects something like : {d '2010-07-08' }

The display of the date text in a control is not necessarily like that, so we have to adapt.

For example with French (France) they are displayed with the format DD/MM/YY.

So I use something like:

.filter = """DateCommande"" >= {d '" & year(sDate) & "-" & month(sDate) & "-" & day(sDate) & "' }"

See an example:

  • Open the GestionEvt in the OpenFormDateFilterFromDataGrid.odb attached.
  • Select one record and
  • Double-click a row header in the datagrid to open the Commandes form filtered on the selected date.

HTH - Regards

edit flag offensive delete link more


Although I have been using this for some time

( "DATE" >= '2016-01-01' AND "DATE" <= '2016-01-31' )

I now know (after Mri) this works identically:

( "DATE" >=  { d '2016-01-01' } AND "DATE" <= '2016-01-31' )

The important factor here is the format of the date.

Ratslinger gravatar imageRatslinger ( 2016-03-18 15:13:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-03-14 09:22:25 +0200

Seen: 388 times

Last updated: Mar 18 '16