Solved Syntax error searching by dates

[zelarra821]

Hello.

I have the following code to lookup a value in the table. What I do is look up the maximum date in a custom function (UltimaVezPresionArterial), which I then use as criteria to look up the last value in another custom function (UltimaPresionArterialTomada). It does not give me the expected value (I get the text box empty), and I think it has to do with the format of the dates, but I am not able to fix it.

Function UltimaPresionArterialTomada() As Variant
    On Error GoTo err_lbl
    UltimaPresionArterialTomada = Format(DLookup("Sistolica", "T09PresionArterial", "Fecha=#" & UltimaVezPresionArterial & "#"), "0 mm Hg") _
                & " / " & Format(DLookup("Diastolica", "T09PresionArterial", "Fecha=#" & UltimaVezPresionArterial & "#"), "0 mm Hg")
Salida:
    Exit Function
err_lbl:
    MsgBox "UltimaPresionArterialTomada: " & Err.Number & " " & Err.Description, vbInformation, NombreBD
    Resume Salida
End Function

Function UltimaVezPresionArterial() As Date
    On Error GoTo err_lbl
    UltimaVezPresionArterial = DMax("Fecha", "T09PresionArterial")
    MsgBox UltimaVezPresionArterial
Salida:
    Exit Function
err_lbl:
    MsgBox "UltimaVezPresionArterial: " & Err.Number & " " & Err.Description, vbInformation, NombreBD
    Resume Salida
End Function

Can someone help me to solve it?

Thanks and Merry Christmas.

 

[Minty]

Put the criteria into a string variable and inspect it before sticking it into the dlookup()
That should show you what's happening.

 

[zelarra821]

Debug.Print "Fecha=#" & UltimaVezPresionArterial & "#"

That's the result:

Fecha=#09/12/2022 11:38:02#

 

[zelarra821]

I think I have to format the date in order to work

 

[cheekybuddha]

I think I have to format the date in order to work

"Fecha=" & Format(UltimaVezPresionArterial, "\#yyyy\-mm\-dd hh:nn:ss\#")

Dates passed to SQL (or WHERE clause parameters of DLookup() etc functions) must be in an unambiguous format (yyyy-mm-dd or mm/dd/yyyy), irrespective of your local date settings or any format set on the table field.

hth

d

 

[zelarra821]

Hi, thanks for your answer. If I test this code, it gives me Null

Debug.Print DLookup("Sistolica", "T09PresionArterial", "Format([Fecha],'mm/dd/yyyy')=#" & Format(UltimaVezPresionArterial, "mm/dd/yyyy") & "#")

I don't understand why, I don't know what I'm doing bad.

 

[cheekybuddha]

Debug.Print DLookup("Sistolica", "T09PresionArterial", "[Fecha]=#" & Format(UltimaVezPresionArterial, "mm/dd/yyyy") & "#")

SQL engine knows Fecha is a date. It doesn't know the value you are comparing it to is a date, but expects it to be in a particular format.

 

[cheekybuddha]

Also, follow Minty's suggestion from Post #2 and inspect the variables at each stage:

Debug.Print UltimaVezPresionArterial
Debug.Print "[Fecha]=#" & Format(UltimaVezPresionArterial, "mm/dd/yyyy") & "#"
Debug.Print DLookup("Sistolica", "T09PresionArterial", "[Fecha]=#" & Format(UltimaVezPresionArterial, "mm/dd/yyyy") & "#")

 

[zelarra821]

Code:Copy to clipboard

Debug.Print UltimaVezPresionArterial
Debug.Print "[Fecha]=#" & Format(UltimaVezPresionArterial, "mm/dd/yyyy") & "#"
Debug.Print DLookup("Sistolica", "T09PresionArterial", "[Fecha]=#" & Format(UltimaVezPresionArterial, "mm/dd/yyyy") & "#")

The result is this:

09/12/2022 11:38:02
[Fecha]=#12/09/2022#
Nulo

Could you tell me where the error is?

 

[zelarra821]

Solved using a @MajP's function

UltimaPresionArterialTomada = Format(DLookup("Sistolica", "T09PresionArterial", "[Fecha]=" & CSql(UltimaVezPresionArterial)), "0 mm Hg") _
& " / " & Format(DLookup("Diastolica", "T09PresionArterial", "[Fecha]=" & CSql(UltimaVezPresionArterial)), "0 mm Hg")

 

[zelarra821]

Public Function CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType) As String
    'Can be used when the Value is subtyped. For example you pass a declared variable
    Const SqlNull       As String = "Null"
    Dim Sql             As String
    
    'If the Sql_type is not passed then use the data type of the value
    If Trim(Value & " ") = "" Then
      CSql = SqlNull
    Else
         If Sql_Type = sdt_UseSubType Then
           Select Case varType(Value)
             Case vbEmpty, vbNull
               Sql_Type = sdt_Null
             Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte
               Sql_Type = sdt_Numeric
             Case vbDate
               Sql_Type = sdt_date
             Case vbString
               Sql_Type = sdt_text
             Case vbBoolean
               Sql_Type = sdt_Boolean
             Case Else
               Sql_Type = sdt_Null
           End Select
         End If
         
        Select Case Sql_Type
           Case sdt_text
                 Sql = Replace(Trim(Value), "'", "''")
                 If Sql = "" Then
                     Sql = SqlNull
                 Else
                     Sql = " '" & Sql & "'"
                 End If
           Case sdt_Numeric
                 If IsNumeric(Value) Then
                  Sql = CStr(Value)
                 Else
                  MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation, NombreBD
                  Exit Function
                 End If
           Case sdt_date
                 If IsDate(Value) Then
                     If Int(CDate(Value)) = Value Then
                        Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
                     Else
                        Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
                     End If
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a date data type", vbInformation, NombreBD
                   Exit Function
                 End If
           Case sdt_Boolean
                 If Value = "True" Or Value = "False" Or Value = -1 Or Value = 0 Or Value = "Yes" Or Value = "No" Then
                   If Value = "True" Or Value = "Yes" Then Value = -1
                   If Value = "False" Or Value = "No" Then Value = 0
                   Sql = str(Value)
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a boolean data type", vbInformation, NombreBD
                   Exit Function
                 End If
            Case sdt_Null
              Sql = SqlNull
        End Select
          CSql = Trim(Sql)
   End If
    
End Function

 

[cheekybuddha]

Solved using a @MajP's function

Congratulations, glad you got it sorted!

Merry Christmas!

 

[cheekybuddha]

Could you tell me where the error is?

My guess, without seeing your data, is that the time component is important. Try with the formatting I suggested originally in Post #5:

Debug.Print UltimaVezPresionArterial
Debug.Print "Fecha=" & Format(UltimaVezPresionArterial, "\#yyyy\-mm\-dd hh:nn:ss\#")
Debug.Print DLookup("Sistolica", "T09PresionArterial", "Fecha=" & Format(UltimaVezPresionArterial, "\#yyyy\-mm\-dd hh:nn:ss\#"))

 

[zelarra821]

Thanks for the suggestion.