!num# error with custom function

[Timtropolis]

Greetings all.

First, wasn't sure whether to post this here or under queries so I'm posting here so sorry in adavance if its the wrong area.

Ok, that being said, here's my problem. In a application that I'm developing an Access 2010, I have created a query which has a column that calls a function and passes two arguments to the function.

Net_Accrued_Income_Amt: Get_NetAccruedIncomeAmt([t_fps_fd].[fd_id],[t_fps_fd].[csh_calc_mthd_id])


My custom function then takes these items and uses the first argument as part of a where clause to in a recordset.

The second is used as a flag in a Select statement. See the code below.

Public Function Get_NetAccruedIncomeAmt(ByVal FUND_id As Integer, ByVal csh_calc_mthd_id As Integer)

    On Error GoTo MyFunctioinMessage_Error

    Dim dbs3 As DAO.Database
    Dim rst3 As DAO.Recordset
    Dim strSQL As String
    Dim Ctr As Integer
    Dim x, y As Double
    
    
    Set dbs3 = CurrentDb
    
    strSQL = "SELECT t_fps_fd.*, t_fps_fd_val.* FROM t_fps_fd INNER JOIN t_fps_fd_val ON t_fps_fd.fd_id = t_fps_fd_val.fd_id"
    strSQL = strSQL = "WHERE t_fps_fd.fd_id = FUND_ID" '<--- 1st argument 
    
    Set rst3 = dbs3.OpenRecordset(strSQL, dbOpenDynaset)
    
    rst3.MoveFirst
                                                          
    Do Until rst3.EOF
        With rst3
            Select Case csh_calc_mthd_id '<--- 2nd argument
                Case Is = 1  
                    x = (!prr_udstr_incm_am + !dstr_incm_am + !tot_incm_am) - !tot_xpns_am
                    If !eqal_csh_am_usg_fl = 0 Then
                        x = x + !eqal_csh_am
                        Get_NetAccruedIncomeAmt = x
                    Else
                        Get_NetAccruedIncomeAmt = x
                    End If
                    
                Case Is = 2 
                    x = !csh_fgn_ccy_am + !mm_insm_am + !acrl_incm_am + !net_stl_pend_trd_am
                    x = x + !sum_cptl_ast_sld_prch_am + !othr_ast_agr_am + !liab_agr_am
                    Get_NetAccruedIncomeAmt = x

..........

What is happening is that I'm getting a #NUM! error in my column, the code isn't even executing as i'm not able to trap anything. I've verified that the two arguments being passed are in the correct datatype.
I have also ran the function on its own (no arguments passed, hard coded values I needed) and it worked fine, so i'm assuming I'm doing something wrong with regards to passing arguments, etc.

Any help would be most welcome.

TIA,

Tim

 

[jzwp22]

First, you have a couple of syntax errors in the strSQL (shown in red below). The second equal sign should be an ampersand (&) since you are concatenating the first part of strSQL to the second. Also, you need a space between the " and the WHERE (shown in green).

strSQL = "SELECT t_fps_fd.*, t_fps_fd_val.* FROM t_fps_fd INNER JOIN t_fps_fd_val ON t_fps_fd.fd_id = t_fps_fd_val.fd_id"
strSQL = strSQL [COLOR="Red"]= [/COLOR][COLOR="SeaGreen"]"W[/COLOR]HERE t_fps_fd.fd_id = FUND_ID" '<--- 1st argument

Secondly, since the Fund_ID is a variable, it cannot be contained within the double quotes. See area in blue

strSQL = "SELECT t_fps_fd.*, t_fps_fd_val.* FROM t_fps_fd INNER JOIN t_fps_fd_val ON t_fps_fd.fd_id = t_fps_fd_val.fd_id"
strSQL = strSQL [COLOR="Red"]& [/COLOR][COLOR="SeaGreen"]" W[/COLOR]HERE t_fps_fd.fd_id =[COLOR="Blue"]" &[/COLOR] FUND_ID

I recommend adding a debug.print statement after you construct the query. That way if the code makes it past the query, you can see exactly what was created. The debug.print statement prints to the VBA immediate window. You can even copy & paste the SQL text from the immediate window into a new query to test the query that was created.

debug.print strSQL

 

[Timtropolis]

TY for catch and prompt reply JZ.
Made the changes but still getting the same error.

 

[jzwp22]

I did make some edits to my original post, so I don't know if you saw the post before or after my edits. Does the debug.print strSQL return a viable query?

 

[Timtropolis]

Ok, that worked and unfortunately I'm getting the dreaded '3061' Too few parameters, Expected 1 error. I have some bookmarked links with regards to this problem so let me investigate those and I'll post back here once I get a solution.

Thanks again JZ *cheers*

 

[jzwp22]

That error usually means that there is a parameter for the query that is not yet satisfied. Assuming that t_fps_fd and t_fps_fd_val are both tables and not queries, it would suggest that there is a spelling error in the query or a field you reference from the recordset later on in your code. Where in the code does the 3061 error occur?

 

[Timtropolis]

Hey JZ,

sorry for the delay... more endless meetings

Success!!

Here's what I did:


The code was bombing out on the line:

Set rst3 = dbs3.OpenRecordset(strSQL, dbOpenDynaset)

I went ahead and simplified my strSQL by creating a query that joins all the information into a staging table. I then created the following SQL for use with my strSQL variable:

strSQL = "SELECT * FROM zzz_tempfunction WHERE zzz_tempfunction.fd_id = " & FUND_id

This fixed it and allowed me to get results.

*Does the happy dance*

Much thanks for your help JZ

 

[jzwp22]

You're welcome. Glad you got it worked out!