Solved Syntax error in a function for a query

zelarra821

Registered User.
Local time
Today, 21:54
Joined
Jan 14, 2019
Messages
850
Let's see, I'm trying to adapt a function that I had made for a form to find the value of the previous record in a query, and it gives me the syntax error that an operator is missing in "Id <" & Id:
Code:
Public Function Diferencia(CampoARestar As String) As Variant
    Dim regAnterior As Variant
    If IsNull(CampoARestar) Then Exit Function
    regAnterior = Nz(DMax(Replace(CampoARestar, ",", "."), "TPrecio", "Id<" &amp; Id), 0)
    If regAnterior = 0 Then
        Diferencia = regAnterior
    Else
        Diferencia = CampoARestar - regAnterior
    End If
End Function

What I want to get is the following:

Feb 5, 2020 5.6 -> null

May 8, 2020 7.5 -> 1.9

Jul 6, 2020 6.5 -> -1

The field to subtract is "Price". I have to use the replace because it has commas. And what fails is the criteria.

As I said before, this way of doing it should work because I have it adapted for a form.

What am I doing wrong?

Greetings.
 
Not leaving space between field/control and operand?
Put the criteria into a string variable and debug.print it.?
 
Code:
Public Function Diferencia(CampoARestar As String) As Variant
    Dim regAnterior As Variant
    Dim StrSQL As String
    StrSQL = "Id<" & Id
    Debug.Print StrSQL
    If IsNull(CampoARestar) Then Exit Function
    regAnterior = Nz(DMax(Replace("Peso", ",", "."), "TPeso", "Id< " & Id), 0)
    If regAnterior = 0 Then
        Diferencia = regAnterior
    Else
        Diferencia = CampoARestar - regAnterior
    End If
End Function

Id<
 
Code:
Public Function Diferencia(CampoARestar As String) As Variant
    Dim regAnterior As Variant
    Dim StrSQL As String
    StrSQL = "Id<" & Id
    Debug.Print StrSQL
    If IsNull(CampoARestar) Then Exit Function
    regAnterior = Nz(DMax(Replace("Peso", ",", "."), "TPeso", "Id< " & Id), 0)
    If regAnterior = 0 Then
        Diferencia = regAnterior
    Else
        Diferencia = CampoARestar - regAnterior
    End If
End Function

Id<
Hi. I don't see the definition for your id variable.

Add Option Explicit at the top of your module to see if you get an error because of that.
 
I don't see the definition for your id variable
That was.

Code:
Public Function Diferencia(CampoARestar As String, Id As Byte) As Single
    Dim regAnterior As Variant
    Dim StrSQL As String
    StrSQL = "[Id]"
    Debug.Print StrSQL
    If IsNull(CampoARestar) Then Exit Function
    regAnterior = Nz(DMax(Replace("Peso", ",", "."), "TPeso", "Id<" & Id), 0)
    'Diferencia = regAnterior
    If regAnterior = 0 Then
        Diferencia = regAnterior
    Else
        Diferencia = CampoARestar - regAnterior
    End If
End Function
 
That was.

Code:
Public Function Diferencia(CampoARestar As String, Id As Byte) As Single
    Dim regAnterior As Variant
    Dim StrSQL As String
    StrSQL = "[Id]"
    Debug.Print StrSQL
    If IsNull(CampoARestar) Then Exit Function
    regAnterior = Nz(DMax(Replace("Peso", ",", "."), "TPeso", "Id<" & Id), 0)
    'Diferencia = regAnterior
    If regAnterior = 0 Then
        Diferencia = regAnterior
    Else
        Diferencia = CampoARestar - regAnterior
    End If
End Function
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Back
Top Bottom