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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.