dear experts,,
I just created a form to search some criterias in my database.(refer pic 01) but it is not connected to any table or query. so I want to make run a query first and secondly run a report using query results.
I created a module ( following my friend arnel gp ) but it doesn't work at all.
there are some errors I could see.
I have uploaded my database too.
highly appreciated if anyone can help me.
thank you.
the codes are as below,
Option Compare Database
Option Explicit
Public Function fnCustomFilter()
Dim qd As DAO.QueryDef
Dim strQuery As String
Dim strFilter As String
strQuery = CurrentDb.QueryDefs("MASCHERARICERCAquery").SQL
strFilter = "(1=1)"
If IsNull([Forms]![MASCHERARICERCAquery]![DATADIINIZIO]) = False And _
IsNull([Forms]![MASCHERARICERCAquery]![DATADIFINE]) = False Then _
strFilter = strFilter & _
" And (AMMINISTRAZIONE.DATA) Between #" & _
Format([Forms]![MASCHERARICERCAquery]![DATADIINIZIO], "mm/dd/yyyy") & "# And #" & _
Format([Forms]![MASCHERARICERCAquery]![DATADIFINE], "mm/dd/yyyy") & "#"
If IsNull([Forms]![MASCHERARICERCAquery]![ENTRATE]) = False Then _
strFilter = strFilter & _
" And (AMMINISTRAZIONE.CAMPI_ENTRATE) Like " & Chr(34) & [Forms]![MASCHERARICERCAquery]![ENTRATE] & "*" & Chr(34)
If IsNull([Forms]![MASCHERARICERCAquery]![USCITE]) = False Then _
strFilter = strFilter & _
Set qd = CurrentDb.QueryDefs("MASCHERARICERCAquery")
qd.SQL = Replace(strQuery, ";", "") & " Where " & strFilter
Set qd = Nothing
End Function
I just created a form to search some criterias in my database.(refer pic 01) but it is not connected to any table or query. so I want to make run a query first and secondly run a report using query results.
I created a module ( following my friend arnel gp ) but it doesn't work at all.
there are some errors I could see.
I have uploaded my database too.
highly appreciated if anyone can help me.
thank you.
the codes are as below,
Option Compare Database
Option Explicit
Public Function fnCustomFilter()
Dim qd As DAO.QueryDef
Dim strQuery As String
Dim strFilter As String
strQuery = CurrentDb.QueryDefs("MASCHERARICERCAquery").SQL
strFilter = "(1=1)"
If IsNull([Forms]![MASCHERARICERCAquery]![DATADIINIZIO]) = False And _
IsNull([Forms]![MASCHERARICERCAquery]![DATADIFINE]) = False Then _
strFilter = strFilter & _
" And (AMMINISTRAZIONE.DATA) Between #" & _
Format([Forms]![MASCHERARICERCAquery]![DATADIINIZIO], "mm/dd/yyyy") & "# And #" & _
Format([Forms]![MASCHERARICERCAquery]![DATADIFINE], "mm/dd/yyyy") & "#"
If IsNull([Forms]![MASCHERARICERCAquery]![ENTRATE]) = False Then _
strFilter = strFilter & _
" And (AMMINISTRAZIONE.CAMPI_ENTRATE) Like " & Chr(34) & [Forms]![MASCHERARICERCAquery]![ENTRATE] & "*" & Chr(34)
If IsNull([Forms]![MASCHERARICERCAquery]![USCITE]) = False Then _
strFilter = strFilter & _
Set qd = CurrentDb.QueryDefs("MASCHERARICERCAquery")
qd.SQL = Replace(strQuery, ";", "") & " Where " & strFilter
Set qd = Nothing
End Function