filter in a form by using a checkbox which is not saved in a table

[mar7632]

Hi everyone,



In my form I build a filter by code which has this structure:



All the data filtered is saved in a table called "aa_DatosRecibidos". Recently, I have introduced (in my form) a checkbox (it is not saved in my table) which will show a tick (True) if a certain field called "Coste_estimado" has a value in there. My intention is to show by filtering all the values located in my field "Coste_estimado" by using a checkbox: yes -> it shows all records which has a "coste_estimado" value regardless the value itself. So, I came up with this chekbox idea (I am not sure if it is the best option, maybe there is a easy way).

Private Sub Form_Current()
Me.VerificacionCosteEstimado.Value = False 'Automatically the checkbox is filled when a coste_estimado has a value
If Not IsNull(coste_estimado) Then
Me.VerificacionCosteEstimado.Value = True
End If
End Sub

The filter has this structure:

For the button "Borrar Filtro" (clear filter)

Private Sub cmdBorrar_Click() 'in order to clean the form

Me.chkVerificacionCosteEstimado.Value = Null 'checkbox

 

Me.cboPlanta.Value = Null 'text

Me.txtEspesorInicial.Value = Null 'initial range value (number)
Me.txtEspesorFinal.Value = Null 'final range value (number)

Me.txtFechaInicial.Value = Null 'initial range value (date)
Me.txtFechaFinal.Value = Null 'final range value (date)

Me.FilterOn = False

For the button "FILTRO" (apply filter)

Dim vVerificacionCosteEstimado As Variant 'checkboxDim vPlanta As String 'text

Dim vEspesorInicial As Variant 'number
Dim vEspesorFinal As VariantDim vFechaInicial As Variant 'date
Dim vFechaFinal As Variant

Dim vLargo As Integer
Dim miFiltro As String
'......

vVerificacionCosteEstimado = Nz(Me.chkVerificacionCosteEstimado.Value, "")


vPlanta = Nz(Me.cboPlanta.Value, "") 'text

vEspesorInicial = Nz(Me.txtEsfuerzoInicial, "")
vEspesorFinal = Nz(Me.txtEsfuerzoFinal, "")

vFechaInicial = Nz(Me.txtFechaInicial, "")
vFechaFinal = Nz(Me.txtFechaFinal, "")

'.....

miFiltro = ""

If vVerificacionCosteEstimado <> "" Then
miFiltro = miFiltro & " AND [VerificacionCosteEstimado]=" & vVerificacionCosteEstimado
End If 'i think that here is the problem because VerificacionCosteEstimado is not saved a field

If vPlanta <> "" Then
miFiltro = "AND [planta_grupo] LIKE '*" & vPlanta & "*'"
End If


If vEspesorInicial <> "" Then
miFiltro = miFiltro & " AND [ESPESOR]>=" & vEspesorInicial
End If
If vEspesorFinal <> "" Then
miFiltro = miFiltro & " AND [ESPESOR]<=" & vEspesorFinal
End If

 
If vFechaInicial <> "" Then
miFiltro = miFiltro & " AND [fecha_consulta]>=#" & Format(vFechaInicial, "mm/dd/yyyy") & "#"
End If
If vFechaFinal <> "" Then
miFiltro = miFiltro & " AND [fecha_consulta]<=#" & Format(vFechaFinal, "mm/dd/yyyy") & "#"
End If

'....
vLargo = Len(miFiltro)
If vLargo > 0 Then
miFiltro = Right(miFiltro, vLargo - 4)
End If


If DCount("*", "aa_DatosRecibidos", miFiltro) = 0 Then 'The error is shown in this line
MsgBox "Los valores introducidos en el filtro no se corresponden con ningún registro. Haz click sobre 'BORRAR FILTRO' y prueba de nuevo."  ' 
Else
Me.Filter = miFiltro
Me.FilterOn = True
End If

End Sub

The checkbox works as intended in my form. When "Coste_estimado" has a value then the checkbox shows a tick. But, when I apply the filter it does not work as intended. I suppose that the main problem is that this checkbox is not saved in my table "aa_DatoRecibidos". is not a field but a ordinary checkbox.


Any idea to solve this little problem?

Thank you in advance

[rpeare]

I would approach this a different way. I think I would alter the underlying query to have a calculated field like

Show: iif([checkbox] = -1 and not isnull([checkboxfield]), 1, iif([checkbox = 0 or isnull([checkbox]), 1, 0))

and have a criteria on the SHOW field of 1

[mar7632]

Hi again,

I have changed a little bit how I am looking for this numerical field called "coste_estimado". By using a comb box (cboTieneCoste) with two values: "SÍ" it shows all records which have any value introduced in "coste_estimado". And "NO" it shows all records which don´t have any value introduced in "coste_estimado".

The code I wrote:

If vTieneCoste = "SÍ" Then
miFiltro = miFiltro & "  AND Nz([coste_estimado], 0)>0 "
ElseIf vTieneCoste = "NO" Then
miFiltro = miFiltro & " AND Nz([coste_estimado], 0)=0 "
End If

When I selected "NO" it worked fine but when I chose "SÍ" an error is reported. '3075': 'D Nz([coste_estimado], 0)>0 '.

Any idea?

Thank you in advance.

[code]

[mar7632]

Okey, I have found the problem. and it was an extra space in the code:

If vTieneCoste = "SÍ" Then
miFiltro = miFiltro & " AND Nz([coste_estimado], 0)>0 "   'HERE
ElseIf vTieneCoste = "NO" Then
miFiltro = miFiltro & " AND Nz([coste_estimado], 0)=0 "
End If

Finally!