wrong number of arguments or invalid property assignment" on a DLOOKUP in ms/acces

Hi.
On my app I am getting the error "wrong number of arguments or invalid property assignment" on a DLOOKUP, and I simply can'r find why.

The lookup table looks quite alright as per the fields in the below screenshots:

ss1
ss2
and the error is:

ss3
The actual source is:




Private Sub Form_Open(Cancel As Integer)

    Dim strUserID As String, _
        strNumeroContrato As String
        
    Dim dblMontante As Double, _
        dblMontantePago As Double, _
        dblMontantePagoFCFA As Double, _
        dblMontantePagoEUR As Double, _
        dblMontantePrevioFCFA As Double, _
        dblMontantePrevioEUR  As Double
    
'On Error GoTo errorTrap

    Call logMe("Form_Open_F22_OrdemPagamento", "start")
    If CurrentProject.AllForms("F91_LoggedUser").IsLoaded Then
        strUserID = Nz(Forms![F91_LoggedUser]![CurrentUserID], "")
        If IsNull(Forms![F91_LoggedUser]![CurrentUserID]) Or _
                Forms![F91_LoggedUser]![CurrentUserID] = "" Then
                strUserID = "##NA##"
        Else
            strUserID = Forms![F91_LoggedUser]![CurrentUserID]
        End If
    End If
    dblMontante = 0
    dblMontantePrevioFCFA = DLookup("T03_TotalPagamentosAnterioresFCFA", _
                                    "T03_Contratos", _
                                    "T03_NumeroContrato = " & Nz(Me.F07_NumeroContrato, "###SemContrato###"))
    dblMontantePrevioFCFA = Nz(dblMontantePrevioFCFA, 0)
    dblMontantePrevioEUR = DLookup("T03_TotalPagamentosAnterioresEUR", _
                                    "T03_Contratos", _
                                    "T03_NumeroContrato = " & Nz(Me.F07_NumeroContrato), "###SemContrato###")
    dblMontantePrevioEUR = Nz(dblMontantePrevioEUR, 0)
    dblMontante = Nz(Me.F07_MontantePagar, 0)
    dblMontantePagoFCFA = DSum("T07_MontantePagarFCFA", _
                            "T07_OrdemPagamento", _
                            "T03_NumeroContrato = " & Nz(Me.F07_NumeroContrato), "###SemContrato###")
    dblMontantePagoFCFA = Nz(dblMontantePagoFCFA, 0) + dblMontantePrevioFCFA
    dblMontantePagoEUR = DSum("T07_MontantePagarEUR", _
                            "T07_OrdemPagamento", _
                            "T07_NumeroContrato = " & Nz(Me.F07_NumeroContrato), 0)
    dblMontantePagoEUR = Nz(dblMontantePagoEUR, 0) + dblMontantePrevioEUR
    Me.F07_MontantePagarExtenso = AchaExtenso(dblMontante, Me.F07_Moeda)
    Call logMe("Form_Open_F22_OrdemPagamento", "end")
    
    Exit Sub
    
errorTrap:
    If Err.Number <> 2501 Then
        MsgBox "[Form_Open_F22_OrdemPagamento]." & _
                "[" & Err.Number & "].[" & Err.Description & "]"
        Call logMe("errorTrap Form_Open_F22_OrdemPagamento", _
                    "[Form_Open_F22_OrdemPagamento]." & "[" & _
                    Err.Number & "].[" & Err.Description & "]")
    End If
    Resume Next
End Sub

Open in new window





can anyone help?
João serras-pereiraAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
I see what might be a couple of issues:

1.  the code on lines 26-28 contains a NZ function which looks like it is supposed to append a numeric value to the rest of the string, but if that numeric values is NULL, it wants to append a text string.
Nz(Me.F07_NumeroContrato, "###SemContrato###"))

Open in new window

But if that is the case, then it should look like the following, with single quotes wrapping "##...##" string
Nz(Me.F07_NumeroContrato, "'###SemContrato###'"))

Open in new window

2.  lines 30-32: you put the first ) in the wrong spot, and still have the same problem with the NULL value.  Instead of:
Nz(Me.F07_NumeroContrato), "###SemContrato###")

Open in new window

Try:
Nz(Me.F07_NumeroContrato, "'###SemContrato###'"))

Open in new window

0
 
Gustav BrockCIOCommented:
It's pretty clear. DLookup takes three arguments:

DLookup("T03_TotalPagamentosAnterioresFCFA", _
                                    "T03_Contratos", _
                                    "T03_NumeroContrato = " & Nz(Me.F07_NumeroContrato, "###SemContrato###"))

Open in new window

0
 
PatHartmanCommented:
Don't the commas on the first two lines belong inside the quotes?  They need to be part of the string.
0
 
João serras-pereiraAuthor Commented:
Hi...
The value of the search coli is always a string, but I had, historically a wrong field name for it "...numero...". It we look into lines 26-28, they are OK and no compile error:

That was what was inducing me in error.

So I quoted, as I should, the strings on "'" and voilá!

Still, I am scratching my head on why the first lines were Ok (!)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.