Creación de filtros con MS-Access

Una lupa

Realizar una fórmula con filtros es algo complicado con MS-Access. Veremos como simplificar la escritura de filtros mediante el uso de la función "Filtrar".

Esta función tiene en cuenta el tipo de datos del valor. Por ejemplo, si el valor es de texto, entrecomilla dicho valor y duplica las comillas que pudiese contener. Además la función "Filtrar" facilita la creación de filtros compuestos de varios criterios que se tienen que cumplir al mismo tiempo.

Instalación

  • Descargar la base de datos filtros_access.7z… (Está comprimido con 7-zip). Luego importar el módulo "ModuloFiltros" de esta base de datos a nuestra propia base de datos.
  • Otra opción es crear el módulo "ModuloFiltros" a partir del código fuente que se detalla más abajo

Ejemplos

Simplificación del uso de las funciones "DLookUp", "DCount", "DMax", etc.

Supongamos que tenemos un cuadro combinado para seleccionar un cliente y queremos mostrar en un cuadro de texto el número de pedidos de dicho cliente. El campo IdCliente es de tipo texto. La solución habitual sería:

=DCOUNT("IdPedido";"Pedidos";"IdCliente='" & [IdCliente] & "'")

Gracias a la nueva función "Filtrar" la solución es:

=DCOUNT("IdPedido";"Pedidos";Filtrar([IdCliente]))

NOTA IMPORTANTE: Estamos suponiendo que el cuadro combinado se llama "IdCliente" para que coincida con el nombre del campo que queremos filtrar.

Supongamos que tenemos un segundo cuadro combinado para seleccionar el empleado. Tendremos que filtrar por el cliente y por el empleado. Hay que tener en cuenta que IdCliente es de tipo texto y que IdEmpleado es de tipo numérico. La fórmula habitual:

=DCOUNT("IdPedido";"Pedidos","IdCliente='" & [IdCliente] & "' AND IdEmpleado=" & [IdEmpleado])

Queda simplificada así:

=DCOUNT("IdPedido";"Pedidos";Filtrar([IdCliente];[IdEmpleado]))

¡Mucho más fácil ahora!

Si los cuadros combinados no se llaman igual que los campos podemos utilizar la función "Multifiltrar":

=DCOUNT("IdPedido"; "Pedidos"; Multifiltrar("IdCliente";[ComboCliente];"IdEmpleado";[ComboEmpleado]))

Mostrar un informe filtrado a partir de un formulario

Supongamos que tenemos en un formulario tres cuadros combinados: IdCategoría, IdCliente y IdEmpleado. Queremos utilizar estos cuadros combinados como posibles filtros de un informe de pedidos. Para ello al hacer clic en un botón de comando ponemos el siguiente código:

Dim filtro As String
If Not IsNull(Me.IdCategoría) Then
    If filtro <> "" Then filtro = filtro & " AND "
    filtro = filtro & "IdCategoría=" & Me.IdCategoría
End If
If Not IsNull(Me.IdCliente) Then
    If filtro <> "" Then filtro = filtro & " AND "
    filtro = filtro & "IdCliente='" & Me.IdCliente & "'"
End If
If Not IsNull(Me.IdEmpleado) Then
    If filtro <> "" Then filtro = filtro & " AND "
    filtro = filtro & "IdEmpleado=" & Me.IdEmpleado
End If
DoCmd.OpenReport "Pedidos", acViewPreview, WhereCondition:=filtro

Usando la función "Filtrar" se simplifica en una sóla línea:

DoCmd.OpenReport "Pedidos", acViewPreview, WhereCondition:=Filtrar(Me.IdCategoría, Me.IdCliente, Me.IdEmpleado)

NOTA: Si en el cuadro combinado no se selecciona ningún valor significa todos los valores

De forma alternativa se puede usar la función "Multifiltrar" dónde hay que ser más explícito. La ventaja es que podemos usar otros operadores de comparación distintos del igual y que los cuadros combinados no tienen porque llamarse igual que el campo. Por ejemplo:

DoCmd.OpenReport "Pedidos", acViewPreview, WhereCondition:=Multifiltrar("IdCategoría",Me.ComboCategoría, "IdCliente>=",Me.ComboCliente, "IdEmpleado",Me.ComboEmpleado)

Código fuente del módulo "ModuloFiltros"

Option Compare Database
Option Explicit

'Módulo: ModuloFiltros
'Autor: Francisco Cascales <fco@proinf.net>
'Versión: 0.02

Function Filtrar(ParamArray controles()) As String
'Crea un filtro a partir del nombre y valor de los controles de formulario.
'Formatea adecuadamente los textos, las fecha y los valores decimales al formato SQL de MS-Access
'Ejemplos:
' Un ComboBox llamado IdCliente con el valor "ANTON": Filtrar([IdCliente]) --> "IdCliente='ANTON'"
' Un TextBox llamado FechaPedido con el valor #29/03/2007# : Filtrar([FechaPedido]) --> "FechaPedido=#03/27/2007#"
' Ambos filtros a la vez: Filtrar([IdCliente],[FechaPedido]) --> "(IdCliente='ANTON') AND (FechaPedido=#03/27/2007#)"
'29-III-2007, Francisco Cascales <fco@proinf.net>

Dim control
For Each control In controles
Call AgregarFiltro(Filtrar, FiltrarPor(control.Name, control.Value))
Next

End Function

Function Multifiltrar(ParamArray parejasCampoValor()) As String
'Crea un filtro a partir de parejas de campo y valor.
'El campo puede incluir el operador de comparación.
'Si se omite el operador de comparación en el campo se supone que es el de igualdad.
'Formatea adecuadamente los textos, las fecha y los valores decimales al formato SQL de MS-Access
'Ejemplos:
' Multifiltrar("IdCliente","ANTON","IdProducto",5) --> "(IdCliente='ANTON') AND (IdProducto=5)"
' Multifiltrar("IdCliente","ANTON","IdProducto",null) --> "(IdCliente='ANTON')"
' Multifiltrar("FechaPedido>=",date(),"FechaPedido<=",date()-7) --> "(FechaPedido>=#12/6/2007#) AND (FechaPedido<=#11/29/2007#)"
'6-XII-2007, Francisco Cascales <fco@proinf.net>

Dim indice As Long

For indice = LBound(parejasCampoValor) To UBound(parejasCampoValor) Step 2
If indice + 1 > UBound(parejasCampoValor) Then Exit For
Dim campo As String: campo = parejasCampoValor(indice)
Dim valor As Variant: valor = parejasCampoValor(indice + 1)
Call AgregarFiltro(Multifiltrar, FiltrarPor(campo, valor))
Next

End Function

Function FiltrarPor(ByVal nombreCampo As String, ByVal valor As Variant) As String
'Crea un filtro, o cláusula WHERE de SQL, a partir del campo y del valor de ese campo
'teniendo en cuenta el tipo de datos: nulo, numérico, fecha, patrón o texto.
'Si se omite el operador de comparación en nombreCampo se supone que es el de igualdad.
'Ejemplos:
' FiltrarPor("Precio",2.32) --> "Precio=2.32"
' FiltrarPor("Nombre grupo","Rolling's") --> "[Nombre grupo]='Rolling''s'"
' FiltrarPor("Nombre grupo>=","Rolling's") --> "[Nombre grupo]>='Rolling''s'"
' FiltrarPor("Direccion","C*") --> "Direccion LIKE 'C*'"

Const COMILLA = "'"
Const COMA = ","
Const PUNTO = "."
Const SIMBOLOS_COMPARACION = "<>="

Dim indice As Integer
Dim operador As String
Dim indicePrimerSimboloComparacion As Integer

'Quitar posibles espacios del principio y del final
nombreCampo = Trim(nombreCampo)

'Buscar si el campo ya incluye algún operador de comparación
For indice = 1 To Len(SIMBOLOS_COMPARACION)
indicePrimerSimboloComparacion = InStr(nombreCampo, Mid(SIMBOLOS_COMPARACION, indice, 1))
If indicePrimerSimboloComparacion <> 0 Then Exit For
Next
operador = IIf(indicePrimerSimboloComparacion = 0, "=", "")

'Poner el nombre del campo entre corchetes en el caso que esté
' formado por varias palabras y no estuviesen puesto ya los corchetes
If InStr(nombreCampo, " ") And Left(nombreCampo, 1) <> "[" Then
If indicePrimerSimboloComparacion = 0 Then
nombreCampo = "[" & nombreCampo & "]"
Else
nombreCampo = "[" & Left(nombreCampo, indicePrimerSimboloComparacion - 1) & "]" & _
Mid(nombreCampo, indicePrimerSimboloComparacion)
End If
End If

If IsNull(valor) Then
FiltrarPor = "" 'nombreCampo & " IS NULL"
ElseIf valor = "" Then
FiltrarPor = ""
ElseIf IsNumeric(valor) Then
'Cambiar la coma por punto para que coincida con el sistema estadounidense
valor = Replace(Nz(valor, 0), COMA, PUNTO)
FiltrarPor = nombreCampo & operador & valor
ElseIf IsDate(valor) Then
'Poner el formato de fecha al estilo estadounidense
FiltrarPor = nombreCampo & operador & Format(CDate(valor), "\#mm/dd/yyyy\#")
ElseIf InStr(valor, "*") Then 'Or InStr(valor, "?") Then
'Duplicar las COMILLA simples
valor = Replace(valor, COMILLA, COMILLA & COMILLA)
operador = IIf(operador = "", "", " LIKE ")
FiltrarPor = nombreCampo & operador & COMILLA & valor & COMILLA
Else
'Duplicar las COMILLA simples
valor = Replace(valor, COMILLA, COMILLA & COMILLA)
FiltrarPor = nombreCampo & operador & COMILLA & valor & COMILLA
End If
End Function

Function UnionY(ParamArray criterios()) As String
'Une varios criterios con "AND"
'Ejemplos:
' UnionY("IdCliente='ANTON'","IdCategoría=1") --> "(IdCliente='ANTON') AND (IdCategoría=1)"
' UnionY(Filtrar([IdProveedor],Filtrar[IdCategoría]) --> "(IdProveedor=4) AND (IdCategoría=23)"

UnionY = "(" & Join(criterios, ") AND (") & ")"
End Function

Function UnionO(ParamArray criterios()) As String
'Une varios criterios con "OR"
'Ejemplos:
' UnionO("IdCliente='ANTON'","IdCliente='BOLID'") --> "(IdCliente='ANTON') OR (IdCliente='BOLID')"
' UnionO(Filtrar([IdCliente],Filtrar[IdProveedor]) --> "(IdCliente='DUMON') OR (IdProveedor=7)"
UnionO = "(" & Join(criterios, ") AND (") & ")"
End Function

Function AgregarFiltroPor(ByRef filtro As String, ByVal nombreCampo As String, ByVal valor As Variant, Optional ByVal operador As String = "AND") As String
'Agrega un nuevo criterio a un filtro a través del nombre del campo y su valor
'Ejemplo:
' Dim filtro as String
' call AgregarFiltro(filtro, "IdCliente", "ANTON") --> "(IdCliente='ANTON')"
' call AgregarFiltro(filtro, "FechaPedido", date) --> "(IdCliente='ANTON') AND (FechaPedido=#03/29/2007#)"

AgregarFiltroPor = AgregarFiltro(filtro, FiltrarPor(nombreCampo, valor), operador)
End Function

Function AgregarFiltro(ByRef filtro As String, ByVal criterio As String, Optional ByVal operador As String = "AND") As String
'Agrega un nuevo criterio a un filtro. Utiliza el operador "AND" por omisión
'Ejemplo:
' Dim filtro as String
' Call AgregarFiltro(filtro "IdEmpleado=10") --> "(IdEmpleado=10)"
' Call AgregarFiltro(filtro, "IdProducto=23") --> "(IdEmpleado=10) AND (IdProducto=23)"

Const ESPACIO = " "

criterio = Trim(criterio)

If criterio = "" Then
AgregarFiltro = filtro
Else
criterio = "(" & criterio & ")"
If filtro = "" Then
filtro = criterio
Else
filtro = filtro & ESPACIO & operador & ESPACIO & criterio
End If
End If

AgregarFiltro = filtro
End Function
Proinf.net, ©2003-2017 ci 3.1.6 (CC) Esta obra está bajo una licencia de Creative Commons Este software está sujeto a la CC-GNU GPL