Construir adecuadamente las expresiones de filtro o de búsqueda es una tarea que se le atraganta a más de uno.

Las cadenas de texto entre comillas, las fechas entre almohadillas y en formato americano, los decimales con punto en vez de coma... Sin embargo, cuando diseñamos una consulta en vista diseño es mucho más sencillo; parece que Access entiende nuestro idioma y le podemos pasar fechas sin formatear, cadenas de texto sin comillas, incluso le podemos hablar en español (Entre... y...) o en inglés (Between... and ...) que lo interpreta correctamente. Si luego leemos el código SQL generado, vemos que Access se ha ocupado de formatear, delimitar y traducir nuestras expresiones para que funcionen correctamente.

Lo mismo ocurre si ponemos parámetros a nuestra consulta y se los asignamos con código, como explica Juan M. Afán de Ribera en su artículo Consulta con parámetros.

Pues bien, ese mismo mecanismo interno para construir correctamente las cadenas de criterio está disponible, desde las primeras versiones de Access, utilizando la función BuildCriteria. Porqué entonces nos rompemos la cabeza montando nuestras cadenas "a mano" es un misterio al que la única explicación que encuentro es que aparentemente funciona mal con campos de texto. Recalco lo de aparentemente porque volveré sobre ello más adelante.

La ayuda de Access cuenta de BuildCritera cosas como:

Después de maravillarnos viendo con qué facilidad BuildCriteria resuelve los habituales problemas con los campos de fecha o decimales al construir criterio, nos llevamos un gran chasco cuando intentamos hacer lo mismo con las cadenas de texto problemáticas, las que contienen comillas o apóstrofes en su interior, incluso si contiene paréntesis se produce el error.

¿Fallo? Pues no.

BuildCriteria está pensado para evaluar las expresiones que el usuario introduce; no sólo formatea correctamente la cadena, sino que también es capaza de evaluar operadores como  =,>, <, Entre.., y.., o.. o incluso funciones que se incluyan en la expresión. Esto en un campo numérico es sencillo: cualquier carácter que no sea numérico o el separador decimal se evalúa, con los campos de fecha parecido, pero las cadenas de texto pueden contener cualquier carácter y entonces la única forma de distinguir los operadores es que nosotros mismos diferenciemos el texto añadiéndole las comillas, de lo contrario, Buildcriteria trata de interpretarlo de la manera más sencilla.

Si pasamos como argumento:

Entre Pinto y Valdemoro BulidCriteria lo interpreta como Between "Pinto" And "Valdemoro"

Por tanto, si estamos filtrando, nos quedarán aquellos registros cuyo campo filtrado sea mayor o igual que "Pinto" y menor o igual que "Valdemoro".

Si, por el contrario, lo delimitamos entre comillas:

"Entre Pinto y Valdemoro" BuildCriteria lo interpreta como = "Entre Pinto y Valdemoro"

Pero ¿Y, si el primer ejemplo fuera el contenido de un campo de una tabla y quisiera encontrar el registro que contiene Entre "Pinto" y "Valdemoro"? En ese caso, BuildCriteria no es capaz de resolver las comillas directamente y tendríamos que pasárselas nosotros y, como se trata de comillas dentro de comillas, tendríamos que recurrir a montar la cadena con Chr(34) y se nos acabó la simplicidad.

Entonces, no nos encontramos ante un fallo, sino ante que es imposible interpretar dentro de una cadena de texto qué es texto y que son operadores si no le ponemos delimitadores. Se contemplan las expresiones más sencillas o más comunes y, a partir de ahí, la tarea pasa el usuario.

Sin embargo, en muchas ocasiones, por ejemplo cuando el criterio de búsqueda lo obtenemos de un cuadro combinado con Limitar a lista = Sí,  lo que pretendemos es que nos monten correctamente la cadena de búsqueda, arreglando comillas y apóstrofes, y nada más; no necesitamos ningún operador distinto de =. ¿Lo puede hacer BuildCriteria? Directamente, no, pero podemos hacernos una función, que llamaremos ConstruyeCriterio y que lo que hace es preparar el argumento que pasamos a BuildCriteria para que funcione correctamente, y, como eso supone cierta pérdida de funcionalidad por otro lado, le añadimos un par de parámetros para decidir si usamos BuildCriteria sin más, con su funcionalidad original, o si la arreglamos para que monte las cadenas complicadas.

He elaborado una función muy sencilla, ContruyeCriterio() que lo que hace es llamar a BuildCriteria, pero con algunos arreglos:

• Utiliza una enumeración DataTypeEnum, en vez de un entero como BuildCriteria en el parámetro FieldType. Curiosamente, BuildCriteria no lo usa y, sin embargo los valores que admite como válidos son precisamente esos. Supongo que será que es así por tratarse de una función muy antigua.

• Utiliza un Variant en vez de un String en el parámetro Expresion.

• Añade dos parámetros opcionales:

  • ProcesarComillas: Boolean, indica si queremos procesar las comillas de la Expresion antes de pasárselas a BuildCriteria.

  • Operador: String, Si ProcesarComillas es verdadero, Buildcriteria no interpretará los operadores que pueda haber dentro de la Expresion, por tanto, si queremos añadir un operador podemos hacerlo pasándolo, aparte, en este parámetro.

'--------------------------------------------------------------------------------
' Procedure : ConstruyeCriterio
' DateTime  : 22/10/06
' Author    : José Bengoechea Ibaceta (Chea)
' Purpose   : Llamar a la función BuidCriteria() tratando previamente la expresión 
'             que le pasamos si el campo es de texto y el usuario lo ha decidido
'             así.
'   En los campos de texto, BuildCriteria puede interpretar ciertos caracteres
'   como parte de una expresión y no del literal que se busca, por lo que puede
'   ser necesario que le demos una tratamiento previo a esa cadena de texto antes
'   de pasarla como argumento. Si vamos a utilizar como criterio texto que incluye
'   comillas simples o dobles o paréntesis, debemos tratar previamente ese texto
'   añadiéndole comillas, sin embargo, si no lo necesitamos, es preferible no
'   añadir esas comillas pues con ellas BuildCriteria no puede interpretar ciertas
'   expresiones que pueden ir dentro de este texto.
'
'   Puedes encontrar más explicaciones sobre esta función en http://jbengoechea.com
'   Estás autorizado a utilizar este código dentro de una aplicación
'   siempre que esta nota de autor permanezca inalterada.
'   En el caso de querer publicarlo en una página Web, por favor,
'   contactar con el autor en
'
'       UA@bengoechea.net
'-------------------------------------------------------------------------------

Public Function ConstruyeCriterio(sCampo As String, tipoDato As DataTypeEnum, _
    Expresion As Variant, Optional ProcesarComillas As Boolean = True, Optional _
    Operador As String) As String
If Operador <> "" And ProcesarComillas = False Then
    MsgBox _
        "El parámetro 'Operador' sólo se usar si 'ProcesarComillas' es verdadero"
End If
If (tipoDato = dbText Or tipoDato = dbMemo Or tipoDato = dbChar) And _
    ProcesarComillas = True Then
    If InStr(Expresion, Chr(34)) = 0 Then
        Expresion = Chr(34) & Expresion & Chr(34)
    Else
        Expresion = Replace(Expresion, Chr(34), Chr(34) & " & chr (34) & " & _
            Chr(34))
        Expresion = Chr(34) & Expresion & Chr(34)
    End If
    If Operador <> "" Then
        Expresion = Operador & Expresion
    End If
End If
ConstruyeCriterio = BuildCriteria(sCampo, tipoDato, Expresion)

End Function

En consultas o en propiedades de controles, es decir, en la interfaz más próxima al usuario, Access no reconoce las constantes de las enumeraciones, por lo que debemos sustituir el nombre de las constantes por su valor equivalente. Para buscar los valores de la enumeración DataTypeEnum lo más sencillo es buscar en el Examinador de Objetos, al que podemos llegar en el menú Ver del editor de VB, pero, por si acaso, añado aquí los posibles valores.

Valores para DataTypeEnum

Bajarse la aplicación demo