Module is not working (1 Viewer)

[Dhanu]

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

 

[Cronk]

For a start your code does not compile. You can't string a number of separate code lines with ampersands. So replace the relevant bit with the following construct.

If IsNull([Forms]![MASCHERARICERCAquery]![USCITE]) = False Then
   strFilter = strFilter
   Set qd = CurrentDb.QueryDefs("MASCHERARICERCAquery")
   qd.SQL = Replace(strQuery, ";", "") & " Where " & strFilter
End If

And while you are doing that, you might consider how the code is supposed to execute when the procedure is not called by the embedded macro.

 

[Dhanu]

For a start your code does not compile. You can't string a number of separate code lines with ampersands. So replace the relevant bit with the following construct.

If IsNull([Forms]![MASCHERARICERCAquery]![USCITE]) = False Then
   strFilter = strFilter
   Set qd = CurrentDb.QueryDefs("MASCHERARICERCAquery")
   qd.SQL = Replace(strQuery, ";", "") & " Where " & strFilter
End If

And while you are doing that, you might consider how the code is supposed to execute when the procedure is not called by the embedded macro.

hi cronk, thanks for the kind reply. I did the changes as you said.but when run a query, it shows all the details in my database.

can you write complete codes for me? (i don't know anything about modules I just copy and pasted a module my friend arnel gp has created.)
and I have doubt about below code. it was my friend written earlier in another module.

Chr(34)
thanks.


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
End If

 

[arnelgp]

Hello my friend, how are You?

 

[Dhanu]

Hello my friend, how are You?

Heyy ciaooo arnel gp. I'm pretty well thanks.
I checked the database you modified. It's working perfectly as always.

Thank you very much. I didn't want to bother you always.that is why i published a post in general.

Thanks again.have a nice day.

 

[arnelgp]

No problem about that. I always answer when I know how.

 

[Dhanu]

Hello my friend, how are You?[/QUOTE

Hi arnel,

can you please explain me how to run below 3 reports using this database?

thanks

REPORT 1 (PERIODO)

Data
ENTRATE
USCITE

REPORT 2 (ENTRATE)

Data
TOTALE ENTRATE
ALTERNANZA
ALTRE ATTIVITA'
ALTRE ENTRATE
ALTRI SERVIZI
CONTRIBUTI
FIDELIZZAZIONI
GITE E ATTIVITA' ESTERNE
PROGETTI SCUOLA
SERVIZI TURISTICI
TIROCINI
TOUR & ESCURSIONI
VISITE GUIDATE GRUPPO


REPORT 3 (USCITE)

Data
TOTALE USCITE
AFFITTO
ASSICURAZIONI
BENI DI CONSUMI
BENI STRUMENTALI
COMMISSIONI
CONSULENZE
INGRESSI BIGLIETTERIA
IVA - TASSE - IMPOSTE
MATERIALE PROMO
NOLEGGIO TRASPORTO
NOLEGGIO STRUMENTI
PERSONALE
PUBBLICITA'
RISTORAZIONE
SERVIZI WEB
UTENZE
VARIE

 

[arnelgp]

Sorry, you don't have those data in the db you upload. Are there any calculation? Write a sample of the report in excel and more details about them. You know my mail.

 

[Dhanu]

Sorry, you don't have those data in the db you upload. Are there any calculation? Write a sample of the report in excel and more details about them. You know my mail.

ok, i will contact you.