Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85

    Unanswered: Using own created function in sql statement

    Access 2007 vba
    I created a function called search contactpers is a separate module. I'm trying to use this function in a SQL statement but it doesn't recognise it as an in built function. I get an error "function .... is a not recognised built in function name" when I execute the query.
    Example
    tblrel = "SELECT g_contac.custid, g_contac.name, g_contac.www, "
    tblrel = tblrel & "search_cpers(custid) as cntpers "
    tblrel = tblrel & "FROM g_contac "

    rs_rel.Open tblrel, conn

    Function search_cpers is looking in the person table for the name belonging to the custid

    Any suggestions

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the function has to be declared public in a module accessable to the MDB/MDE which wishes to use that function.

    however if all you are doing is looking for the name then why not do a simple join to retrieve the persons name

    eg
    join persons on persons.personID = g_contac.custID
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Thanks for your reply.
    The function is declared public.
    I used the function when I connected to the database using ODBC and the select statement worked. But I'm trying to skip the ODBC connection and just work with a sqloledb connection.
    I agree with you that a join with c_person is much easier but I have to retrieve more. If I get this working the rest will work also.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I dunno then... public functions are definitely visible to queries.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85

    recognised built in

    I think I found out what goes wrong. I made the function search_cpers in VBA module but I connect to sql db and within the sql db there is no function called search_cpers

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah, you were trying to get SQL Server to interpret the function!

    Thanks for letting us know, I'll try to remember this next time someone's function doesn't work
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •