Solved Syntax error in a function for a query (1 Viewer)

zelarra821

Registered User.
Local time
Today, 17:54
Joined
Jan 14, 2019
Messages
803
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:54
Joined
Sep 21, 2011
Messages
14,044
Not leaving space between field/control and operand?
Put the criteria into a string variable and debug.print it.?
 

zelarra821

Registered User.
Local time
Today, 17:54
Joined
Jan 14, 2019
Messages
803
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<
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:54
Joined
Oct 29, 2018
Messages
21,358
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.
 

zelarra821

Registered User.
Local time
Today, 17:54
Joined
Jan 14, 2019
Messages
803
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:54
Joined
Oct 29, 2018
Messages
21,358
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

Top Bottom