Filter by date via macro

[cmpd]

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
else
oFormCtl.ApplyFilter = False
end if 
oFormCtl.Reload 
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!

[Ratslinger]

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.

[cmpd]

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]

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’

[Ratslinger]

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]

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.

[PYS]

Hi

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

[Ratslinger]

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.