Help with calling a function

[nello87to]

Hi all,


I am new about Access and VB, I have a problem with a calling to a function from a query.

This is my query:

SELECT
"???" AS nome,
"???" AS ente_componente,
"???" AS stato_classificazione,
EXP_APPROACH AS calcolo_rischio,
calcola_tipo_strumento([forma_tecnica_proven];[prestiti_rotativi]) AS tipo_strumento,
"???" AS data_xxx,
"???" AS trib
INTO Output_Tabella1
FROM campi_per_tabella1;

The problem is in the calling to the functioncalcola_tipo_strumento for a "sintax error".

This is the function:

Option Compare Database
Option Explicit

Public Function calcola_tipo_strumento(r111 As String, r222 As String) As String
If r111 = "11" Or r111 = "53" Then
calcola_tipo_strumento = "conto"
ElseIf r111 = "58" Or r111 = "59" Or r111 = "60" Then
calcola_tipo_strumento = "carta"
ElseIf r111 = "99" Then
If r222 = "1" Then
calcola_tipo_strumento = "aaa"
ElseIf r222 = "0" Then
calcola_tipo_strumento = "bbb"
End If
End If
End Function

Can someone tell me what i can look to solve this error?
Thanks a lot,
Nello

[ranman256]

the function is just a field you create in the query, but you must add the parameters (fields)
BUT you may not want to define the parameters as strings. It will fail if the field is NULL. So make them Variants.

Public Function calcola_tipo_strumento(r111 , r222 ) As String

select calcola_tipo_strumento([field1], [field2]) as MyFieldName from table

[CJ_London]

looks like your function is expecting strings which are actually numbers, so perhaps [forma_tecnica_proven] and/or [prestiti_rotativi] are numbers?

if so, change r111 As String, r222 As String to r111 As long, r222 As long

and change all instances of

r111 = "11" or some other number to

r111 = 11

Alternatively try

calcola_tipo_strumento([forma_tecnica_proven] & "";[prestiti_rotativi] & "") AS tipo_strumento,


Also learn to use the code tags (#) to preserve formatting, makes it easier for everyone to read

Public Function calcola_tipo_strumento(r111 As String, r222 As String) As String
    If r111 = "11" Or r111 = "53" Then
           calcola_tipo_strumento = "conto"
    ElseIf r111 = "58" Or r111 = "59" Or r111 = "60" Then
           calcola_tipo_strumento = "carta"
    ElseIf r111 = "99" Then
           If r222 = "1" Then
                  calcola_tipo_strumento = "aaa"
           ElseIf r222 = "0" Then
                  calcola_tipo_strumento = "bbb"
           End If
    End If
 End Function

[JoeM]

Are the forma_tecnica_proven and prestiti_rotativi fields in the campi_per_tabella1 table?
What is the data type of each?

I am not sure of which version of Access you are using, but mine uses commas and not semi-colons as field delimiters (but I have seen some European versions that use semi-colons like you have in your code).
So, if I change it to:

calcola_tipo_strumento([forma_tecnica_proven],[prestiti_rotativi]) AS tipo_strumento,

your code works for me.

[davegri]

Public Function calcola_tipo_strumento(r111, r222) As String
select case r111
    case "11", "53"
        calcola_tipo_strumento = "conto"
    case "58", "59", "60"
        calcola_tipo_strumento = "carta"
    case "99"
        select case r222
            case "1"
                calcola_tipo_strumento = "aaa"
            case "0"
                calcola_tipo_strumento = "bbb"
            case else
        end select
end select
end function

Your function is much easier to understand if coded as above with Select Case rather than nested IF-Then-Else.
Your query makes no sense. Explain what it is intended to do.

[nello87to]

thank you all for the attention.
I have solved about the sintax error and i will put all these cerrection for have a better code.
I have now this kind of error: function 'calcola_tipo_strumento' not defined in the expression. Do you know what are the causes of this error?
I think the query doesn't recognize the function, it's possible?
i have simplify query and code to identify what's wrong:

function:
Option Compare Database
Option Explicit

Public Function calcola_tipo_strumento(r111) As String
If r111 = "11" Or r111 = "53" Then
calcola_tipo_strumento = "Conto"
End If
End Function

query:
SELECT calcola_tipo_strumento([forma_tecnica_proven]) AS tipo_strumento FROM campi_per_tabella1;

ps.
i want extract with a query some columns from a table, and for one of these column i want calculate the value with a function.
I think this is a stupid error, but i am a cobol programmer

[CJ_London]

you haven't said where the code is located - it need to be a standard module (not a form or class module) and the module has to have a different name to the function.

If this is your simplified code, you don't even need a function

SELECT iif([forma_tecnica_proven] in ("11","53"),"Conto","") AS tipo_strumento FROM campi_per_tabella1;

this assumes that [forma_tecnica_proven] is a field in your campi_per_tabella1 table and is a string, not a number

[nello87to]

Thank you a lot!
this was my mistake: the module has to have a different name to the function!

thank you all and i wish you a nice 15 august =)
Nello