Consultas con parámetros

A menudo aparece en los grupos de noticias una pregunta sobre alguien que tiene problemas a la hora de construir los criterios de una sentencia SQL, ya sea porque en esos criterios se utilizan fechas, o bien porque a lo mejor hay algún campo de texto que contiene una comilla simple, o tal vez porque hace servir campo de tipo doble (con su coma decimal, lo cual es interpretado erróneamente por el motor de base de datos como un separador de campos o argumentos)

En el caso de las fechas debemos saber que internamente Access sólo entiende el formato americano, es decir “mm/dd/yyyy”. Pero el hecho es que nosotros estamos utilizamos el formato “dd/mm/yyyy”. Un lío, vamos… Para evitar problemas debemos formatear nuestras fechas al formato americano.

Por ejemplo, construyamos una sentencia SQL con un criterio de selección basado en fechas:

SQL = "SELECT * FROM Pedidos " _
& "WHERE FechaPedido Between #" _
& Format("04-07-1996", "mm/dd/yyyy") _
& "# And #" & Format("06-05-1998", "mm/dd/yyyy") & "#"

Esa es una forma de resolverlo, pero si nuestra sentencia SQL es larga todo ese abrir y cerrar comillas, poner símbolos de almohadilla “#”, etc, acaba llevándonos a cometer más de un fallo.

Otra situación en la que nos podemos encontrar es un campo de texto que contenga un apóstrofe (comilla simple). Por ejemplo:

sDestinatario = "B's Beverages"

SQL = "SELECT * FROM Pedidos " _
& "WHERE Destinatario = '" & sDestinatario & "'"

Esa sentencia producirá un error de “error de sintaxis, falta operador…”. Para resolverlo debemos sustituir la comilla simple por un par de comillas, así:

sDestinatario = "B's Beverages"
' reemplazamos las comillas simples por un par 
' de comillas
sDestinatario = Replace(sDestinatario, "'", "''")

SQL = "SELECT * FROM Pedidos " _
& "WHERE Destinatario = '" & sDestinatario & "'"

Otro lío resuelto. Vamos a por el tercero:

Otro caso que se nos puede presentar es utilizar un criterio que usa un valor doble o moneda, cosa muy normal en un país que utiliza la coma como separador decimal. Pero lo que Access entiende es, otra vez, la configuación americana, es decir, la coma como separador de miles y el punto como separador decimal. Esta dificultad la resolveríamos de manera análoga a la anterior, pero en este caso reemplazaríamos la coma por un punto. Por ejemplo:

Dim nCargo As Double
Dim sCargo As String

    ' esto se representa en nuestra configuración
    ' regional como "100,01"
    nCargo = 100.01
    ' reemplazamos la coma por un punto
    sCargo = Replace(nCargo, ",", ".")
    SQL = "SELECT * FROM Pedidos " _
    & "WHERE Cargo > " & sCargo

Por fin hemos resuelto nuestros problemas. Ahora veamos una sentencia SQL que prevea las 3 circunstancias anteriores:

Dim SQL As String
Dim sDestinatario As String
Dim nCargo As Double
Dim sCargo As String

    sDestinatario = "B's Beverages"
    sDestinatario = Replace(sDestinatario, "'", "''")
    
    nCargo = 100.01
    sCargo = Replace(nCargo, ",", ".")
    
    SQL = "SELECT * FROM Pedidos " _
    & "WHERE FechaPedido Between #" _
    & Format("04-07-1996", "mm/dd/yyyy") _
    & "# And #" & Format("06-05-1998", "mm/dd/yyyy") & "#" _
    & " AND Destinatario = '" & sDestinatario & "'" _
    & " AND Cargo > " & sCargo

etc ...

Todo esto hará que nuestro código funcione correctamente.

Pero tenemos otra alternativa, en mi opinión, más limpia, elegante y rápida, y es dejar al motor Jet que haga el trabajo sucio y se encargue él de entenderse con los tipos de datos que necesita y nuestra configuración regional (que tantos dolores de cabeza nos ha causado).

Cogeremos el ejemplo que estábamos desarrollando y lo desarrollaremos dentro de una consulta con parámetros:

Dim SQL As String
Dim sDestinatario As String
Dim fecha1 As String, fecha2 As String
Dim nCargo As Double
Dim qdf As QueryDef
Dim rst As DAO.Recordset
    
    ' construimos una cadena de consulta con
    ' 4 parámetros
    SQL = "SELECT * FROM Pedidos " _
    & "WHERE FechaPedido Between parametro1 And parametro2 " _
    & "AND Destinatario Like parametro3 " _
    & "AND Cargo > parametro4"
    
    ' creamos una consulta temporal
    Set qdf = CurrentDb.CreateQueryDef("", SQL)
    
    ' damos valor a las variables con los datos
    fecha1 = "04-07-1996"
    fecha2 = "06-05-1998"
    sDestinatario = "B's Beverages"
    nCargo = 100.01
    
    ' damos valor a los parámetros, fijaros que
    ' no necesitamos encerrarlos entre "#" ni
    ' convertirlos al formato de fecha americano
    ' ni importará que un parámetro contenga una
    ' comilla simple o una coma como separador
    ' decimal . El motor Jet sabrá qué hacer
    ' en cada uno de los casos
    qdf.Parameters("parametro1") = fecha1
    qdf.Parameters("parametro2") = fecha2
    qdf.Parameters("parametro3") = sDestinatario
    qdf.Parameters("parametro4") = nCargo
    
    ' abrimos un recordset de la consulta
    Set rst = qdf.OpenRecordset
    While Not rst.EOF
        ' imprimimos los resultados
        Debug.Print rst!IdPedido, rst!Destinatario, _
                    rst!FechaPedido, rst!Cargo
        rst.MoveNext
    Wend
    
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing

Pregunta

Es muy interesante lo que comentas… Pero yo estoy trabajando con ADO y me gustaría saber cómo desarrollar una consulta con parámetros en ADO ¿Podrías poner un ejemplo?

Respuesta

Naturalmente. Ahí tienes la misma consulta, pero desarrolada en ADO

Dim SQL As String
Dim sDestinatario As String
Dim fecha1 As String, fecha2 As String
Dim nCargo As Double
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
    
    ' construimos una cadena de consulta con
    ' 4 parámetros
    SQL = "SELECT * FROM Pedidos " _
    & "WHERE FechaPedido Between parametro1 And parametro2 " _
    & "AND Destinatario Like parametro3 " _
    & "AND Cargo > parametro4"
    
    ' damos valor a las variables con los datos
    fecha1 = "04-07-1996"
    fecha2 = "06-05-1998"
    sDestinatario = "B's Beverages"
    nCargo = 100.01
    
    ' abrimos una nueva consulta
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandText = SQL
        .Parameters("parametro1") = fecha1
        .Parameters("parametro2") = fecha2
        .Parameters("parametro3") = sDestinatario
        .Parameters("parametro4") = nCargo
        ' abrimos un recordset de la consulta
        Set rst = .Execute
    End With
    
    While Not rst.EOF
        ' imprimimos los resultados
        Debug.Print rst!IdPedido, rst!Destinatario, _
                    rst!FechaPedido, rst!Cargo
        rst.MoveNext
    Wend
    
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing

Créditos

con el fin de dar a conocer el trabajo de uno de los más grandes en el mundo de Access en español, y tras solicitar su permiso, replico este artículo de Juan M Afán de Ribera. Todos los derechos y autoría corresponden a él. Access & VBA FAQ – Consultas con parámetros (archive.org)