Export risultato maschera filtrata con campo "(Empty)"

Question

Export risultato maschera filtrata con campo "(Empty)"

Anonimo

Ciao a tutti,

come al solito avrei bisogno di un aiuto per sistemare il mio database...

In una maschera ho alcune caselle combinate e caselle di testo che servono a filtrare i dati di una tabella, i risultati del filtro vengono visualizzati nel corpo della maschera. Ho aggiunto anche un pulsante che mi permette di esportare in un file Excel la tabella filtrata.

Tutto ha sempre funzionato molto bene fino a quando, qualche tempo fa, ho inserito nelle varie caselle combinate anche il campo "(Empty)" perché nella tabella a cui applico il filtro questi campi non sono necessariamente compilati.

La maschera filtrata viene visualizzata correttamente, ma l'export del risultato non funziona, ad esempio inserendo in una casella combinata il campo "(Empty)" il foglio Excel risulta vuoto. La domanda è questa:

Come faccio ad inserire il campo "(Empty)" anche nell'export?

Vi posto anche il codice che ho usato per inserire il campo "(Empty)" nelle caselle combinate:

Origine riga casella combinata:

SELECT Nz([Platform].[ID_platform],0) AS Espr2, IIf(IsNull([platform]),"(Empty)",[platform]) AS Espr1

FROM Report LEFT JOIN Platform ON Report.ID_platform = Platform.ID_platform

WHERE (((Nz([ID_discipline],0))=IIf([forms].[Search Report].[cboDiscipline] Is Null,Nz([ID_discipline],0),[forms].[Search Report].[cboDiscipline])) AND ((Nz([ID_division],0))=IIf([forms].[Search Report].[cbodivision] Is Null,Nz([ID_division],0),[forms].[Search Report].[cbodivision])) AND ((Nz([ID_work_task],0))=IIf([forms].[Search Report].[cboWorktask] Is Null,Nz([ID_work_task],0),[forms].[Search Report].[cboWorktask])) AND ((Nz([ID_Analyst],0))=IIf([forms].[Search Report].[cboAnalyst] Is Null,Nz([ID_Analyst],0),[forms].[Search Report].[cboAnalyst])) AND ((Nz([ID_component],0))=IIf([forms].[Search Report].[cboComponent] Is Null,Nz([ID_component],0),[forms].[Search Report].[cboComponent])) AND ((Nz([ID_vehicle_programcode],0))=IIf([forms].[Search Report].[cboVehicle] Is Null,Nz([ID_vehicle_programcode],0),[forms].[Search Report].[cboVehicle])))

GROUP BY Nz([Platform].[ID_platform],0), IIf(IsNull([platform]),"(Empty)",[platform])

ORDER BY IIf(IsNull([platform]),"(Empty)",[platform]);

Costruzione del filtro della maschera:

Private Sub btn_Search_Click()

strFilter = vbNullString

With Me

If Len(.cboPlatform & vbNullString) > 0 Then

If .cboPlatform = 0 Then 

   strFilter = strFilter & " and ID\_platform Is Null" 

Else 

   strFilter = strFilter & " and ID\_platform=" & .cboPlatform & "" 

End If

End If

.

If Len(.txtKeyword & vbNullString) > 0 Then strFilter = strFilter & " and [Report name] like '*" & .txtKeyword & "*'"

If Len(.txtCTSid & vbNullString) > 0 Then strFilter = strFilter & " and [CTS id] like '*" & .txtCTSid & "*'"

If Len(.txtPdescription & vbNullString) > 0 Then strFilter = strFilter & " and [program_description] like '*" & .txtPdescription & "*'"

If Len(.txtFirstdate & vbNullString) > 0 Then strFilter = strFilter & " and [Release date]>=" & CLng(.txtFirstdate)

If Len(.txtSecondate & vbNullString) > 0 Then strFilter = strFilter & " and [Release date]<" & CLng(.txtSecondate) + 1

If Len(strFilter) &gt; 0 Then 

    strFilter = "1 = 1" + strFilter 

    .Filter = strFilter 

    .FilterOn = True 

Else 

    .FilterOn = False 

    .Filter = vbNullString

End If

End With

End Sub

Export in excel alla pressione del pulsante:

Private Sub ExportExcel_Click()

Dim strSql As String

Dim strFilter As String

Dim qdf As DAO.QueryDef

Const queryFilter As String = "QExport"

On Error GoTo Errore

'Sostituisco nel filtro i nomi relativi alle caselle combinate per evitare l'errore Errore di run-time '3079

strFilter = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Me.Filter, "ID_division", "div.[ID_division]"), "ID_discipline", "disc.[ID_discipline]"), _

"ID_platform", "plat.[ID_platform]"), "ID_vehicle_programcode", "veh.ID_vehicle_programcode"), "ID_work_task", "WT.ID_work_task"), _

"ID_component", "comp.ID_component"), "ID_Analyst", "an.ID_Analyst")

If Len(strFilter) &gt; 0 Then

strSql = "SELECT div.division as Division, disc.discipline as Discipline, plat.platform as Platform, veh.vehicle_programcode as ProgramCode, " _

& "WT.work_task as WorkTask, [CTS id], comp.component as Component, program_description as [Program Description], " _

& "an.Analyst AS Analyst, [Report name], [Release date], Repository, Note " _

& "FROM (((((((Report AS R INNER JOIN Division AS div ON R.[ID_division] = div.[ID_division]) " _

& "INNER JOIN Discipline AS disc ON R.[ID_discipline] = disc.[ID_discipline]) " _

& "INNER JOIN Platform AS plat ON plat.[ID_platform] = R.[ID_platform]) " _

& "LEFT JOIN Vehicle_ProgramCode AS veh ON veh.ID_vehicle_programcode = R.ID_vehicle_programcode) " _

& "INNER JOIN [Work task] AS WT ON WT.ID_work_task = R.ID_work_task) " _

& "LEFT JOIN Components AS comp ON comp.ID_component = R.ID_component) " _

& "INNER JOIN Analysts AS an ON an.ID_Analyst = R.ID_Analyst) " _

& "WHERE " & strFilter & " ORDER BY [Release date]"

Else