Excell Functions in an SQL query

Posted on 2005-04-26
Last Modified: 2012-06-27

Is it possible to use the Excell Yield function in an Access SQL statement?
Question by:DaFou
    LVL 26

    Expert Comment

    Yield? I'm not aware of that.
    Short answer would be "no".

    However, you can emulate teh function if you have it's formula...
    LVL 2

    Author Comment

    I have the formula.
    Lets imgine the formula is something like this
    How can I emulate it?

    Be ware though It has to be working in aceess 97 aswell as in access 2003
    LVL 11

    Accepted Solution

    If you are working within Access queries and Access SQL (not SQL Server T-SQL), you could create a custom public function and call it within your query. Eg:

    Public Function myFunc(val1 as double) as double
      dim x as integer, z as integer
      myFunc = ((x*z)/z)+val1
    End Function

    Your query:

    Select fld1, fld2, myFunc(fld3) as newFld
    From tbl1

    Hope this gives you some ideas. If you are working with SQL Server T-SQL, you can create your custom functions in SQL Server just the same.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now